<?php
/*
 * @Author:CYH
 * @Date: 2020-11-09 18:35:24
 * @LastEditTime: 2021-04-22 10:32:27
 * @LastEditors: Please set LastEditors
 * @Description: 水电费用管理control类
 * @FilePath: \dorm_manage_DEV\application\controllers\Cost\ApplicationForm.php
 */
header('Access-Control-Allow-Origin:*');
// 响应类型  
header('Access-Control-Allow-Methods:POST');  
// 响应头设置  
header('Access-Control-Allow-Headers:x-requested-with'); 
require_once 'PHPExcel-1.8\Classes\PHPExcel.php';
require_once 'PHPExcel-1.8\Classes\PHPExcel\IOFactory.php';
// require_once 'PHPExcel\Classes\PHPExcel\Reader\Excel5.php';
require_once 'PHPExcel-1.8\Classes\PHPExcel\Reader\Excel2007.php';

//设置时区
date_default_timezone_set('Asia/Shanghai');

// 水电费用管理control类
class ApplicationForm extends CI_Controller 
{

    // 构造方法
    public function __construct()
    {

        parent::__construct();
        $this->load->helper('url_helper');
        $this->load->library('session');
        $this->load->model('Cost/ApplicationForm_model');
        $this->load->model('Register/Tune_in');//调宿
        $this->load->model('Room/Room_manage');
        $this->load->library('upload');
        $this->load->helper('date');
        $this->load->helper(array('form','url'));
    }


    /**
     * @description: 新增免扣水电申请
     * @param {name, jobNumber, department, area_number, floor_number, room_number, beginDate, endDate, type, remark}
     * @return json:array{'res'=>'','msg'=>'','data'=>''}
     */    
    public function applyCreate()
    {

        $name = $_POST['name'];
        $jobNumber = $_POST['jobNumber'];
        $department = $_POST['department'];
        $area_number = $_POST['area_number'];
        $floor_number = $_POST['floor_number'];
        $room_number = $_POST['room_number'];
        $beginDate = $_POST['beginDate'];
        $endDate = $_POST['endDate'];
        $type = $_POST['type'];
        $remark = $_POST['remark'];

        if(empty($name) || empty($jobNumber) || empty($department) || empty($area_number) || empty($floor_number) || empty($room_number) || empty($beginDate) || empty($endDate)){

            echo json_encode(array('res' => '41000', 'msg' => '必填数据缺失', 'data' => ''));
            return 0;

        }

        
        // 计算天数
        $applyDays = ( $endDate - $beginDate ) / 86400;

        // 存数据
        $data = array(
            'name' => $name,
            'jobNumber' => $jobNumber,
            'department' => $department,
            'area_number' => $area_number,
            'floor_number' => $floor_number,
            'room_number' => $room_number,
            'apply_beginDate' => date('Y-m-d', $beginDate),
            'apply_endDate' => date('Y-m-d', $endDate),
            'apply_days' => $applyDays,
            'apply_type' => $type,
            'remark' => $remark
        );
        
        $return = $this->ApplicationForm_model->create($data);

        echo $return;

        return 0;
        
    }

    /**
     * @description: 查询免扣记录表
     * @param {name, jobNumber, room_number, beginDateBe, beginDateEnd, endDateBe, endDateEnd, type, page}
     * @return json:array{'res'=>'','msg'=>'','data'=>''}
     */    
    public function search()
    {
        
        $name = $_POST['name'];
        $jobNumber = $_POST['jobNumber'];
        $room_number = $_POST['room_number'];
        $beginDateBe = $_POST['beginDateBe'];
        $beginDateEnd = $_POST['beginDateEnd'];
        $endDateBe = $_POST['endDateBe'];
        $endDateEnd = $_POST['endDateEnd'];
        $type = $_POST['type'];
        $page = $_POST['page'];
        
        if( empty($page) )
        {

            echo json_encode(array('res' => '41000', 'msg' => '必填数据缺失', 'data' => ''));
            return 0;

        }

        // 查询数据

        $data = array(
            'name' => $name,
            'jobNumber' => $jobNumber,
            'room_number' => $room_number,
            'beginDatebe' => $beginDateBe,
            'beginDateEnd' => $beginDateEnd,
            'endDateBe' => $endDateBe,
            'endDateEnd' => $endDateEnd,
            'apply_type' => $type
        );
        
        $page = ( $page - 1 ) * 10;

        $return = $this->ApplicationForm_model->search($data, $page);

        echo $return;

        return 0;

    }

    
    /**
     * @description: 拉取房间入住人员
     * @param {area_number, floor_number, room_number}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function room_livepeople()
    {
        $area_number = $_POST['area_number'];
        $floor_number = $_POST['floor_number']; 
        $room_number = $_POST['room_number'];

        // $area_number = "1585";
        // $floor_number = "1"; 
        // $room_number = "601-2";

        $return = $this->ApplicationForm_model->room_livepeople($area_number, $floor_number, $room_number);

        echo $return;

        return 0 ;
    }

    /**
     * @description: 调宿方法
     * @param {CoordinatingDate, name, jobNumber, sex, department, check_inAddress, area_number, floor_number, room_number, bedNumber, room_hotwater, room_coldwater, room_electric, remarks}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */        
    public function tune_in()
    {

        $CoordinatingDate = $_POST['CoordinatingDate'];       //调宿日期
        $name = $_POST['name'];                               //人员姓名
        $jobNumber = $_POST['jobNumber'];                     //工号
        $sex = $_POST['sex'];                                 //性别
        $department = $_POST['department'];                   //部门
        $check_inAddress = $_POST['check_inAddress'];         //入住位置
        $address = $_POST['address'];                         //位置
        $area_number = $_POST['area_number'];                 //区域编号
        $floor_number = $_POST['floor_number'];               //宿舍楼编号
        $dorm_floor = $_POST['dorm_floor'];                   //宿舍楼层号
        $room_number = $_POST['room_number'];                 //宿舍房间号
        $bedNumber = $_POST['bedNumber'];                     //床位号
        $room_hotwater = $_POST['room_hotwater'];             //房间热水表
        $room_coldwater = $_POST['room_coldwater'];           //房间冷水表
        $room_electric = $_POST['room_electric'];             //房间电表
        $remarks = $_POST['remarks'];                         //备注


        // $CoordinatingDate = "2020-11-19";
        // $name = "曹万永";
        // $jobNumber = "R016804";
        // $sex = "1";
        // $department = "自动化装配";
        // $address = "1585,1,4,460,6";
        // $check_inAddress = "1585*1*436*2";
        // $area_number = "1585";
        // $floor_number = "1";
        // $dorm_floor = '4';
        // $room_number = "433";
        // $bedNumber = "4";
        // $room_hotwater = "100";
        // $room_coldwater = "100";
        // $room_electric = "100";
        // $remarks = "测试";
        

        $area_number_address = explode(',', $address)[0];
        $floor_number_address = explode(',', $address)[1];       
        $dorm_floor_address = explode(',', $address)[2];   
        $room_number_address = explode(',', $address)[3];


        $type = 2;
        $arr = array(
            'jobNumber' => $jobNumber,
            'date' => $CoordinatingDate,
            'area_number' => $area_number_address,
            'floor_number' => $floor_number_address,
            'dorm_floor' => $dorm_floor_address,
            'room_number' => $room_number_address,
            'room_hotwater' => $room_hotwater,
            'room_coldwater' => $room_coldwater,
            'room_electric' => $room_electric,
            'type' => $type
        );
        //调用方法计算水电费
        $this->singleUtilitiesCalc($arr);

        $result_json  = $this->Tune_in->tune_in(
            $CoordinatingDate,
            $name,
            $jobNumber,
            $sex,
            $department,
            $check_inAddress,
            $address, 
            $area_number,
            $floor_number,
            $dorm_floor,
            $room_number,
            $bedNumber,
            $room_hotwater,
            $room_coldwater,
            $room_electric,
            $remarks
        );
            
        echo $result_json;
        return 0;
    }

    // public function test()
    // {
    //     $jobNumber = $_POST['jobNumber'];       //调宿日期
    //     $date = $_POST['date'];   

    //     $result_json = $this->calcSignleDay($jobNumber, $date);
            
    //     echo $result_json;

    //     return 0;

    // }
    /**
     * @description: 计算每人人天
     * @param {jobNumber, date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function calcSignleDay($jobNumber = FALSE, $idNumber = FAlSE, $date = FALSE)
    {
        $apply_days = 0;
        $leaveOff_days = 0;
        
        //拉取入住日期
        // $liveDate = $this->ApplicationForm_model->liveDay($jobNumber);
        $liveDate = $this->ApplicationForm_model->liveDay($jobNumber, $idNumber);

        //若入住日期与截止日期同年同月则入住天数 = 截止日期 - 入住日期
        $liveDate = json_decode($liveDate);
        $liveDate = $liveDate->data;
        if(substr($liveDate->liveDate, 0, 4) == date("Y") && substr($liveDate->liveDate, 5, 2) == date("m"))
        {
            $liveDays = ( strtotime($date) - strtotime( $liveDate->liveDate ) ) / 86400;   
        }
        //若入住天数与当前同年不同月则入住天数 = 输入日期 - 当年当月第一天
        else
        {
            $liveDays = ( strtotime($date)- strtotime(date('Y-m-01', strtotime($date))) ) / 86400; 

        }

        //拉取请假单
        $applyDays = $this->ApplicationForm_model->apply($jobNumber, $date);

        $applyDays = json_decode($applyDays);

        $applyDays = $applyDays->data;

        if($applyDays != FALSE)
        {
            for($i=0; $i<count($applyDays); $i++)
            {
                $apply_days += $applyDays[$i]->apply_days;
            }
        }

        $liveDays = $liveDays - $apply_days;


        //拉取销假单
        $leaveOff = $this->ApplicationForm_model->leaveOff($jobNumber, $date);

        $leaveOff = json_decode($leaveOff);

        $leaveOff = $leaveOff->data;

        if($leaveOff != FALSE)
        {
            for($j=0 ;$j<count($leaveOff); $j++)
            {
                $leaveOff_days += $leaveOff[$i]->apply_days;
            }
        }
        $liveDays = $liveDays + $leaveOff_days;      
        return $liveDays;
    }

    /**
     * @description: 计算房间人天
     * @param {area_number, floor_number, dorm_floor, room_number, date, type}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function room_manday($area_number, $floor_number, $dorm_floor, $room_number, $date)
    {
        $liveDays = 0;
        //拉取房间入住人员
        $live = $this->ApplicationForm_model->room_livepeople($area_number, $floor_number, $dorm_floor ,$room_number);
        $live = json_decode($live);
        if($live->res == '40000')
        {
            //取每人人天
            for($i=0; $i<count($live->data); $i++)
            {
                $manday = $this->calcSignleDay($live->data[$i]->jobNumber, $date);
                //宿舍房间人员总人天
                $liveDays = $manday + $liveDays;
            }
        }
        return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data'=> $liveDays));
    }

    /**
     * @description: 楼层总人天
     * @param {area_number, floor_number, dorm_floor, date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function floor_manday($area_number, $floor_number, $dorm_floor, $date)
    {
        $room_mandayAll = 0;

        //取楼层下的所有房间
        $room = $this->Room_manage->floorRoom($area_number, $floor_number, $dorm_floor);
        $room = json_decode($room);

        for($i=0; $i<count($room->data); $i++)
        {
            //调用房间总人天
            $room_manday = $this->room_manday($area_number, $floor_number, $dorm_floor, $room->data[$i]->room_number, $date);
            $room_manday = json_decode($room_manday);
            $room_mandayAll += $room_manday->data;
        }

        if($room_mandayAll)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $room_mandayAll));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '查询无数据', 'data' => ''));
        }
    }

    /**
     * @description: 宿舍总人天
     * @param {area_number, floor_number, date }
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function dorm_manaday($area_number = FALSE, $floor_number = FALSE, $date = FALSE)
    {   
        $room_mandayAll = 0;
        //取宿舍楼有人入住的所有房间
        $room = $this->Room_manage->dorm_room($area_number, $floor_number);
        $room = json_decode($room);

        for($j=3; $j<=6; $j++)
        {
            for($i=0; $i<count($room->data); $i++)
            {
                //调用房间总人天
                $room_manday = $this->room_manday($area_number, $floor_number, $j, $room->data[$i]->room_number, $date);
                $room_manday = json_decode($room_manday);
                $room_mandayAll += $room_manday->data;
            }
        }
        if($room_mandayAll)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $room_mandayAll));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '查询无数据', 'data' => ''));
        }
    }

    /**
     * @description: 退宿/换宿人员费用
     * @param {area_number, floor_number, dorm_floor, room_number, date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function personCost($area_number, $floor_number, $dorm_floor, $room_number, $date)
    {   
        $cost_hotwater = 0;
        $cost_coldwater = 0;
        $cost_electric = 0;

        // 退宿人员费用
        $retreatCost = $this->ApplicationForm_model->retreatCost($area_number, $floor_number, $dorm_floor, $room_number, $date);

        $retreatCost = json_decode($retreatCost);

        $retreatCost = $retreatCost->data;

        // 换宿人员费用
        $tuneinCost = $this->ApplicationForm_model->tuneinCost($area_number, $floor_number, $dorm_floor, $room_number, $date);

        $tuneinCost = json_decode($tuneinCost);

        $tuneinCost = $tuneinCost->data;

        if($retreatCost != FALSE)
        {
            //退宿汇总
            for($i=0; $i<count($retreatCost); $i++)
            {
                //热水费汇总
                $cost_hotwater += ($retreatCost[$i]->person_hotwater);

                //冷水费汇总
                $cost_coldwater += ($retreatCost[$i]->person_coldwater);

                //电费汇总
                $cost_electric += ($retreatCost[$i]->person_electric);
            }
        }
    
        if($tuneinCost != FALSE)
        {
            //换宿汇总
            for($i=0; $i<count($tuneinCost); $i++)
            {
                //热水费汇总
                $cost_hotwater += ($tuneinCost[$i]->person_hotwater);

                //冷水费汇总
                $cost_coldwater += ($tuneinCost[$i]->person_coldwater);

                //电费汇总
                $cost_electric += ($tuneinCost[$i]->person_electric);
            }
        }

        $cost = array(
           'area_number' => $area_number,
           'floor_number' => $floor_number,
           'dorm_floor' => $dorm_floor,
           'room_number' => $room_number,
           'cost_hotwater' => $cost_hotwater,
           'cost_coldwater' => $cost_coldwater,
           'cost_electric' => $cost_electric,
        );
        return $cost;
    }

    /**
     * @description:水电费明细查询 
     * @param {jobNumber, name, room_number, beginDate, endDate, limit}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function mandayDetail()
    {
        $jobNumber = $_POST['jobNumber'];           //工号  
        $name = $_POST['name'];                     //姓名
        $room_number = $_POST['room_number'];       //宿舍房间号
        $beginDate = $_POST['beginDate'];           //开始日期
        $endDate = $_POST['endDate'];               //截止日期
        $limit = $_POST['page'];                    //页数

        // $jobNumber = "";    
        // $name = "";
        // $room_number = "";
        // $beginDate = "";
        // $endDate = "";
        // $limit = "1";
        
        //取总条数
        $totalpage = $this->ApplicationForm_model->mandayDetail_all($jobNumber, $name, $room_number, $beginDate, $endDate);
        $totalpage = json_decode($totalpage);

        $result_json = $this->ApplicationForm_model->mandayDetail($jobNumber, $name, $room_number, $beginDate, $endDate, $limit, $totalpage->data);

        echo $result_json;

        return 0;
    }

    /**
     * @description: 保存人天表
     * @param {area_number, floor_number, dorm_floor, room_number, jobNumber, idNumber, liveDays, type, date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function savemanday($area_number = FALSE, $floor_number = FALSE, $dorm_floor = FALSE, $room_number = FALSE, $jobNumber = FALSE, $idNumber = FALSE, $liveDays = FALSE, $type = FALSE, $date = FALSE)
    {
        $arr = [];

        //根据工号获取入住位置
        // $liveAddress = $this->ApplicationForm_model->liveAddress($jobNumber);

        // $liveAddress = json_decode($liveAddress);

        // $liveAddress = $liveAddress->data;

        //     $area_number = $liveAddress->area_number;
        //     $floor_number = $liveAddress->floor_number;
        //     $dorm_floor = $liveAddress->dorm_floor;
        //     $room_number = $liveAddress->room_number;

            $cost_date = substr($date, 5, 2);

        // //更新到人天表
        // $manday = array(
        //     'area_number' => $area_number,
        //     'floor_number' => $floor_number,
        //     'dorm_floor' => $dorm_floor,
        //     'room_number' => $room_number,
        //     // 'idNumber' => $idNumber,
        //     'jobNumber' => $jobNumber,
        //     'liveDays' => $liveDays,
        //     'date' => date("Y-$cost_date-25"),
        //     'type' => $type
        // );

        //更新到人天表
        $manday = array(
            'area_number' => $area_number,
            'floor_number' => $floor_number,
            'dorm_floor' => $dorm_floor,
            'room_number' => $room_number,
            'idNumber' => $idNumber,
            'jobNumber' => $jobNumber,
            'liveDays' => $liveDays,
            'date' => date("Y-$cost_date-25"),
            'type' => $type
        );

        $result = $this->ApplicationForm_model->savemanday($manday);
        if($result)
        {
            return array('res' => '40000', 'msg' => '更新成功', 'data' => '');
        }
        else
        {
            return array('res' => '40001', 'msg' => '更新失败', 'data' => '');
        }
    }

    /**
     * @description: 每层公共电费
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function Electricty()
    {
        $area_number = $_POST['area_number'];       //区域编号    
        $floor_number = $_POST['floor_number'];     //宿舍楼编号 
        $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
        $beginDate = $_POST['beginDate'];           //开始日期
        $endDate = $_POST['endDate'];               //结束日期

        // $area_number = "";       
        // $floor_number = "";                
        // $dorm_floor = "";    
        // $beginDate = "";   
        // $endDate = "";   

        $result_json = $this->publicElectricty(
            $area_number,   
            $floor_number,               
            $dorm_floor,   
            $beginDate,  
            $endDate    
        );

        echo $result_json;

        return 0;
    }

    /**
     * @description: 每层公共电费
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    //( 楼层总表 - 非公共房间消耗电表度数总和 ) / 总人天 * 个人人天(4号楼)
    //( 宿舍总电表 - 非公共房间消耗电表度数总和 ) / 总人天 * 个人人天(A/B,333,178)    
    public function publicElectricty($area_number, $floor_number, $dorm_floor, $beginDate, $endDate)
    {
        //上个月开始结束日期
        $last_beginDate = date("Y-m-d", strtotime("-1 month",strtotime($beginDate)));
        $last_endDate = date("Y-m-d", strtotime("-1 day",strtotime($beginDate)));

        //楼层总表
        //只有4号楼有各楼层总表,其他都是宿舍总电表
        //本月楼层总表
        if($area_number == '1585' && $floor_number == '1')
        {
            //本月楼层总表
            $month_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
            $month_floorTotal = json_decode($month_floorTotal);
            if($month_floorTotal->res == '40000')
            {
                $sumFloorTotalMonth = $month_floorTotal->data->floor_electricty;
            }
            else
            {
                $sumFloorTotalMonth = 0;
            }

            //上月楼层总表
            $last_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate);
            $last_floorTotal = json_decode($last_floorTotal);
            if($last_floorTotal->res == '40000')
            {
                $sumFloorTotalLastMonth = $last_floorTotal->data->floor_electricty;
            }
            else
            {
                $sumFloorTotalLastMonth = 0;
            }

            //本月实际消耗
            //本月楼层总表 - 上月楼层总表
            $floorTotal = ( $sumFloorTotalMonth - $sumFloorTotalLastMonth ) * 60;

            //本月非公共房间电表度数总和 0.公司承担 1.员工承担 
            $month_electrictyTotal = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate, 1);
            $month_electrictyTotal = json_decode($month_electrictyTotal);
            if($month_electrictyTotal->res == '40000')
            {
                $sumelectrictyTotalMonth = $month_electrictyTotal->data->sumElectricty;
            }
            else
            {
                $sumelectrictyTotalMonth = 0;
            }

            //上月非公共房间电表度数总和 0.公司承担 1.员工承担 
            $last_electrictyTotal = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate, 1);
            $last_electrictyTotal = json_decode($last_electrictyTotal);
            if($last_electrictyTotal->res == '40000')
            {
                $sumelectrictyTotalLastMonth = $last_electrictyTotal->data->sumElectricty;
            }
            else
            {
                $sumelectrictyTotalLastMonth = 0;
            }

            //本月实际消耗
            //本月非公共房间电表 - 上月非公共房间电表
            $sumElectricty = $sumelectrictyTotalMonth - $sumelectrictyTotalLastMonth;
            
            //各楼层总人天
            $floorDays = $this->ApplicationForm_model->SumFloorDays($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
            $floorDays = json_decode($floorDays);
        }
        else
        {
            //本月宿舍总表
            $month_floorTotal = $this->ApplicationForm_model->searchFloor($area_number, $floor_number, $beginDate, $endDate);
            $month_floorTotal = json_decode($month_floorTotal);
            if($month_floorTotal->res == '40000')
            {
                $sumFloorTotalMonth = $month_floorTotal->data->dorm_totalElectricty;
            }
            else
            {
                $sumFloorTotalMonth = 0;
            }

            //上月楼层总表
            $last_floorTotal = $this->ApplicationForm_model->searchFloor($area_number, $floor_number, $last_beginDate, $last_endDate);
            $last_floorTotal = json_decode($last_floorTotal);
            if($last_floorTotal->res == '40000')
            {
                $sumFloorTotalLastMonth = $last_floorTotal->data->dorm_totalElectricty;
            }
            else
            {
                $sumFloorTotalLastMonth = 0;
            }

            //本月实际消耗
            //本月楼层总表 - 上月楼层总表
            if($area_number == '333' && $floor_number == '3')
            {
                $floorTotal = ( $sumFloorTotalMonth - $sumFloorTotalLastMonth ) * 120;
            }
            elseif($area_number == '1585' && $floor_number == '2')
            {
                $floorTotal = ( $sumFloorTotalMonth - $sumFloorTotalLastMonth ) * 40;
            }
            else
            {
                $floorTotal = ( $sumFloorTotalMonth - $sumFloorTotalLastMonth ); 
            }

            //本月非公共房间电表度数总和 0.公司承担 1.员工承担 
            $month_electrictyTotal = $this->ApplicationForm_model->SumRoom($area_number, $floor_number, $beginDate, $endDate, 1);
            $month_electrictyTotal = json_decode($month_electrictyTotal);
            if($month_electrictyTotal->res == '40000')
            {
                $sumelectrictyTotalMonth = $month_electrictyTotal->data->sumElectricty;
            }
            else
            {
                $sumelectrictyTotalMonth = 0;
            }

            //上月非公共房间电表度数总和 0.公司承担 1.员工承担 
            $last_electrictyTotal = $this->ApplicationForm_model->SumRoom($area_number, $floor_number, $last_beginDate, $last_endDate, 1);
            $last_electrictyTotal = json_decode($last_electrictyTotal);
            if($last_electrictyTotal->res == '40000')
            {
                $sumelectrictyTotalLastMonth = $last_electrictyTotal->data->sumElectricty;
            }
            else
            {
                $sumelectrictyTotalLastMonth = 0;
            }

            //本月实际消耗
            //本月非公共房间电表 - 上月非公共房间电表
            $sumElectricty = $sumelectrictyTotalMonth - $sumelectrictyTotalLastMonth;

            //宿舍总人天
            $floorDays = $this->ApplicationForm_model->SumDays($area_number, $floor_number, $beginDate, $endDate);
            $floorDays = json_decode($floorDays);
        }

        //计算
        if($floorDays->data->sumLiveDays != 0)
        {
            $publicElectricty = ($floorTotal - $sumElectricty) / $floorDays->data->sumLiveDays;
        }

        if($publicElectricty<0)
        {
            $publicElectricty = 0;
        }
        
        if($publicElectricty)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $publicElectricty));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '查询无数据', 'data' => ''));
        }
    }

    /**
     * @description: 各楼层公共冷水费(4号楼)
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function coldWater_4()
    {
        $area_number = $_POST['area_number'];       //区域编号    
        $floor_number = $_POST['floor_number'];     //宿舍楼编号 
        $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
        $beginDate = $_POST['beginDate'];           //开始日期
        $endDate = $_POST['endDate'];               //结束日期

        // $area_number = "";       
        // $floor_number = "";                
        // $dorm_floor = "";    
        // $beginDate = "";   
        // $endDate = "";   

        $result_json = $this->publicColdWater_4(
            $area_number,   
            $floor_number,               
            $dorm_floor,   
            $beginDate,  
            $endDate    
        );

        echo $result_json;

        return 0;
    }

    /**
     * @description: 各楼层公共冷水费(4号楼)
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    //(各楼层公共房间冷水表消耗之和 - 洗衣机费用) / 总人天 * 个人人天
    public function publicColdWater_4($area_number, $floor_number, $dorm_floor, $beginDate, $endDate)
    {
        //上个月开始结束日期
        $last_beginDate = date("Y-m-d", strtotime("-1 month",strtotime($beginDate)));
        $last_endDate = date("Y-m-d", strtotime("-1 day",strtotime($beginDate)));

        //本月各楼层公共房间冷水表消耗之和 0.公司承担 1.员工承担 
        //本月水表度数
        $type = 0;
        $month_coldWaterTotal = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate, $type);
        $month_coldWaterTotal = json_decode($month_coldWaterTotal);
        if($month_coldWaterTotal->res == '40000')
        {
            $sumColdWaterMonth = $month_coldWaterTotal->data->sumColdWater;
        }
        else
        {
            $sumColdWaterMonth = 0;
        }

        //上月各楼层公共房间冷水表消耗之和 0.公司承担 1.员工承担 
        //上月水表度数
        $last_coldWaterTotal = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate, $type);
        $last_coldWaterTotal = json_decode($last_coldWaterTotal);
        if($last_coldWaterTotal->res == '40000')
        {
            $SumColdWaterLastMonth = $last_coldWaterTotal->data->sumColdWater;
        }
        else
        {
            $SumColdWaterLastMonth = 0;
        }

        //本月实际消耗 = 本月电表度数 - 上月电表度数
        //本月实际消耗
        $sumColdWater = $sumColdWaterMonth - $SumColdWaterLastMonth;

        //洗衣机费用
        $washingMachine = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $washingMachine = json_decode($washingMachine);

        //各楼层总人天
        $floorDays = $this->ApplicationForm_model->SumFloorDays($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $floorDays = json_decode($floorDays);

        //计算
        $publicColdWater = ($sumColdWater * 7 - $washingMachine->data->washingMachine) / $floorDays->data->sumLiveDays;

        if($publicColdWater < 0)
        {
            $publicColdWater = 0;
        }

        if($publicColdWater)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $publicColdWater));
        }
        else
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => ''));
        }
    }

    /**
     * @description: 各楼层公共冷水费(178)
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function coldWater_178()
    {
        $area_number = $_POST['area_number'];       //区域编号    
        $floor_number = $_POST['floor_number'];     //宿舍楼编号 
        $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
        $beginDate = $_POST['beginDate'];           //开始日期
        $endDate = $_POST['endDate'];               //结束日期

        // $area_number = "";       
        // $floor_number = "";                
        // $dorm_floor = "";    
        // $beginDate = "";   
        // $endDate = "";   

        $result_json = $this->publicColdWater_178(
            $area_number,   
            $floor_number,               
            $dorm_floor,   
            $beginDate,  
            $endDate    
        );

        echo $result_json;

        return 0;
    }

    
    /**
     * @description: 各楼层公共冷水费(178)
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */ 
    //(（宿舍总水表 - 非公共房间消耗水表度数总和 ） * 7 - 洗衣机 ) / 总人天 * 个人人天   
    public function publicColdWater_178($area_number, $floor_number, $dorm_floor, $beginDate, $endDate)
    {
        //上个月开始结束日期
        $last_beginDate = date("Y-m-d", strtotime("-1 month",strtotime($beginDate)));
        $last_endDate = date("Y-m-d", strtotime("-1 day",strtotime($beginDate)));

        //本月宿舍总水表
        $month_floorTotal = $this->ApplicationForm_model->searchFloorMeter_178($area_number, $floor_number, $beginDate, $endDate);
        $month_floorTotal = json_decode($month_floorTotal);
        if($month_floorTotal->res == '40000')
        {
            $floorTotalMonth = $month_floorTotal->data->dorm_totalWater;
        }
        else
        {
            $floorTotalMonth = 0;
        }

        //上月宿舍总水表
        $last_floorTotal = $this->ApplicationForm_model->searchFloorMeter_178($area_number, $floor_number, $last_beginDate, $last_endDate);
        $last_floorTotal = json_decode($last_floorTotal);
        if($last_floorTotal->res == '40000')
        {
            $floorTotalLastMonth = $last_floorTotal->data->dorm_totalWater;
        }
        else
        {
            $floorTotalLastMonth = 0;
        }

        //本月实际宿舍水消耗 
        //本月宿舍总水表 - 上月宿舍总水表
        $floorTotal = $floorTotalMonth - $floorTotalLastMonth;

        //本月非公共房间水表度数总和 0.公司承担 1.员工承担 
        $month_coldWaterTotal = $this->ApplicationForm_model->SumRoom($area_number, $floor_number, $beginDate, $endDate, 1);
        $month_coldWaterTotal = json_decode($month_coldWaterTotal);
        if($month_coldWaterTotal->res == '40000')
        {
            $month_coldWaterTotalAll =  $month_coldWaterTotal->data->sumColdWater;
        }
        else
        {
            $month_coldWaterTotalAll = 0;
        }

        //上月非公共房间水表度数总和 0.公司承担 1.员工承担 
        $last_coldWaterTotal = $this->ApplicationForm_model->SumRoom($area_number, $floor_number, $last_beginDate, $last_endDate, 1);
        $last_coldWaterTotal = json_decode($last_coldWaterTotal);
        if($last_coldWaterTotal->res == '40000')
        {
            $lastmonth_coldWaterTotalAll =  $last_coldWaterTotal->data->sumColdWater;
        }
        else
        {
            $lastmonth_coldWaterTotalAll = 0;
        }

        //本月实际非公共房间消耗
        //本月非公共房间水表度数总和 - 上月非公共房间水表度数总和 
        $coldWaterAll = $month_coldWaterTotalAll - $lastmonth_coldWaterTotalAll;

        //洗衣机费用
        $washingMachine = $this->ApplicationForm_model->searchFloorMeter_178($area_number, $floor_number, $beginDate, $endDate);
        $washingMachine = json_decode($washingMachine);

        // //各楼层总人天
        // $floorDays = $this->ApplicationForm_model->SumFloorDays($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        // $floorDays = json_decode($floorDays);

        //宿舍楼总人天
        $floorDays = $this->ApplicationForm_model->SumDays($area_number, $floor_number, $beginDate, $endDate);
        $floorDays = json_decode($floorDays);


        //计算
        $publicColdWater = ( ( $floorTotal - $coldWaterAll ) * 7 - $washingMachine->data->washingMachine) / $floorDays->data->sumLiveDays;

        if($publicColdWater < 0)
        {
            $publicColdWater = 0; 
        }

        if($publicColdWater)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $publicColdWater));
        }
        else
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => ''));
        }
    }

    /**
     * @description: A/B栋 、333冷水费
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function coldWater_333AB()
    {
        $area_number = $_POST['area_number'];       //区域编号    
        $floor_number = $_POST['floor_number'];     //宿舍楼编号 
        $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
        $beginDate = $_POST['beginDate'];           //开始日期
        $endDate = $_POST['endDate'];               //结束日期

        // $area_number = "";       
        // $floor_number = "";                
        // $dorm_floor = "";    
        // $beginDate = "";   
        // $endDate = "";   

        $result_json = $this->ColdWater_333_AB(
            $area_number,   
            $floor_number,               
            $dorm_floor,   
            $beginDate,  
            $endDate    
        );

        echo $result_json;

        return 0;
    }


    /**
     * @description: A/B栋 333冷水费
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function ColdWater_333_AB($area_number, $floor_number, $dorm_floor, $beginDate, $endDate)
    {
        //上个月开始结束日期
        $last_beginDate = date("Y-m-d", strtotime("-1 month",strtotime($beginDate)));
        $last_endDate = date("Y-m-d", strtotime("-1 day",strtotime($beginDate)));

        if($area_number == '1585' && $floor_number == '2')
        {
            //本月宿舍总水表
            $month_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
            $month_floorTotal = json_decode($month_floorTotal);
            if($month_floorTotal->res == '40000')
            {
                $floorTotalMonth = $month_floorTotal->data->dorm_totalWater;
            }
            else
            {
                $floorTotalMonth = 0;
            }

            //上月宿舍总水表
            $last_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate);
            $last_floorTotal = json_decode($last_floorTotal);
            if($month_floorTotal->res == '40000')
            {
                $floorTotalLastMonth = $last_floorTotal->data->dorm_totalWater;
            }
            else
            {
                $floorTotalLastMonth = 0;
            }

            //本月实际消耗
            //本月宿舍总水表 - 上月宿舍总水表
            $floorTotal = $floorTotalMonth - $floorTotalLastMonth;
        }
        elseif($area_number == '333')
        {
            //本月各楼层总水表
            $month_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
            $month_floorTotal = json_decode($month_floorTotal);
            if($month_floorTotal->res == '40000')
            {
                $floorTotalMonth = $month_floorTotal->data->floor_coldwater;
            }
            else
            {
                $floorTotalMonth = 0;
            }

            //上月各楼层总水表
            $last_floorTotal = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate);
            $last_floorTotal = json_decode($last_floorTotal);
            if($last_floorTotal->res == '40000')
            {
                $floorTotalLastMonth = $last_floorTotal->data->floor_coldwater;
            }
            else
            {
                $floorTotalMonth = 0;
            }

            //本月实际消耗
            //本月宿舍总水表 - 上月宿舍总水表
            $floorTotal = $floorTotalMonth - $floorTotalLastMonth;
        }


        //洗衣机费用
        $washingMachine = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $washingMachine = json_decode($washingMachine);

        //各楼层总人天
        $floorDays = $this->ApplicationForm_model->SumFloorDays($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $floorDays = json_decode($floorDays);
        

        //计算
        $publicColdWater = ($floorTotal * 7 - $washingMachine->data->washingMachine) / $floorDays->data->sumLiveDays;
        // print_r($floorTotal);
        // echo "<pre>";
        // print_r($washingMachine->data->washingMachine);
        // echo "<pre>";
        // print_r($floorDays->data->sumLiveDays);
        // exit;

        if($publicColdWater<0)
        {
            $publicColdWater = 0;
        }

        if($publicColdWater)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $publicColdWater));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '', 'data' => ''));
        }
    }

    /**
     * @description: 333公共热水费
     * @param {area_number, floor_number, dorm_floor, beginDate, endDate}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function public_hotWater_333($area_number, $floor_number, $dorm_floor, $beginDate, $endDate)
    {
        //上个月开始结束日期
        $last_beginDate = date("Y-m-d", strtotime("-1 month",strtotime($beginDate)));
        $last_endDate = date("Y-m-d", strtotime("-1 day",strtotime($beginDate)));

        //上月空气能用电
        $last_airElectricity = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $last_beginDate, $last_endDate);
        $last_airElectricity = json_decode($last_airElectricity);

        if(empty($last_airElectricity->data->airElectricity))
        {
            $airElectricityLastMonth = 0;
        }
        else
        {
            $airElectricityLastMonth = $last_airElectricity->data->airElectricity;
        }

        //本月空气能用电
        $month_airElectricity = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $month_airElectricity = json_decode($month_airElectricity);

        if(empty($month_airElectricity->data->airElectricity))
        {
            $airElectricityMonth = 0;
        }
        else
        {
            $airElectricityMonth = $month_airElectricity->data->airElectricity;
        }

        //本月实际空气能用电消耗
        $airElectricity = $airElectricityMonth - $airElectricityLastMonth;

        //上月各楼层空气能用水之和
        $last_sumAirWater = $this->ApplicationForm_model->sumairelectric($area_number, $floor_number, $last_beginDate, $last_endDate);
        $last_sumAirWater = json_decode($last_sumAirWater);

        if(empty($last_sumAirWater->data->airWater))
        {
            $sumairWaterLastMonth = 0;
        }
        else
        {
            $sumairWaterLastMonth = $last_sumAirWater->data->airWater;
        }

        //本月各楼层空气能用水之和
        $month_sumAirWater = $this->ApplicationForm_model->sumairelectric($area_number, $floor_number, $beginDate, $endDate);
        $month_sumAirWater = json_decode($month_sumAirWater);

        if(empty($month_sumAirWater->data->airWater))
        {
            $sumairWaterMonth = 0;
        }
        else
        {
            $sumairWaterMonth = $month_sumAirWater->data->airWater;
        }

        //本月实际空气能用水消耗
        $sumairWater = $sumairWaterMonth - $sumairWaterLastMonth;

        //上月各楼层空气能用水
        $last_airWater = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor ,$last_beginDate, $last_endDate);
        $last_airWater = json_decode($last_airWater);

        if(empty($last_airWater->data->airWater))
        {
            $airWaterLatsMonth = 0;
        }
        else
        {
            $airWaterLatsMonth = $last_airWater->data->airWater;
        }

        //本月各楼层空气能用水
        $month_airWater = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, $dorm_floor ,$beginDate, $endDate);
        $month_airWater = json_decode($month_airWater);

        if(empty($month_airWater->data->airWater))
        {
            $airWaterMonth = 0;
        }
        else
        {
            $airWaterMonth = $month_airWater->data->airWater;
        }

        $airWater = $airWaterMonth - $airWaterLatsMonth;
        
        //取楼层总人天
        $FloorDays = $this->ApplicationForm_model->SumFloorDays($area_number, $floor_number, $dorm_floor, $beginDate, $endDate);
        $FloorDays = json_decode($FloorDays);


        $publicHotWater = ( ($airElectricity * 60 / ( $sumairWater * 7 ) ) * $airWater) / $FloorDays->data->sumLiveDays;

        if($publicHotWater)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $publicHotWater));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '', 'data' => ''));
        }
    }


    /**
     * @description: 
     * @param {name, jobNumber, room_number, $beginDatebe, beginDateEnd, endDateBe, endDateEnd, type}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function outOfExcel($name = FALSE, $jobNumber = FALSE, $room_number = FALSE, $beginDatebe = FALSE, $beginDateEnd = FALSE, $endDateBe = FALSE, $endDateEnd = FALSE, $type = FALSE)
    {

        $data = array(
            'name' => $name,
            'jobNumber' => $jobNumber,
            'room_number' => $room_number,
            'apply_beginDate' => $beginDatebe,
            'apply_endDate' => $beginDateEnd,
            'endDateBe' => $endDateBe,
            'endDateEnd' => $endDateEnd,
            'apply_type' => $type
        );

        $return = $this->ApplicationForm_model->search($data, FALSE);

        // 导出至EXCEL

        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        $objProps->setDescription("请假/销假记录");  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值   
        $objActSheet->setCellValue('A1', '姓名');  // 字符串内容    
        $objActSheet->setCellValue('B1', '工号');  
        $objActSheet->setCellValue('C1', '部门');  
        $objActSheet->setCellValue('D1', '区域编号');  
        $objActSheet->setCellValue('E1', '宿舍楼编号');  
        $objActSheet->setCellValue('F1', '宿舍房间号');
        $objActSheet->setCellValue('G1', '申请日期开始日期');
        $objActSheet->setCellValue('H1', '申请日期结束日期');
        $objActSheet->setCellValue('I1', '申请天数');
        $objActSheet->setCellValue('J1', '申请类别');
        $objActSheet->setCellValue('K1', '备注');

        $num = 2;
        $data = json_decode($return, true);
        // print_r($data);
        $data = $data['data']['data'];

        for ($i=0; $i < count($data); $i++) { 


            $objActSheet->setCellValueExplicit('A' . $num,$data[$i]['name'] , PHPExcel_Cell_DataType::TYPE_STRING);  

            $objActSheet->setCellValueExplicit('B' . $num,$data[$i]['jobNumber'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('C' . $num,$data[$i]['department'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('D' . $num,$data[$i]['area_number'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('E' . $num,$data[$i]['floor_number'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('F' . $num,$data[$i]['room_number'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('G' . $num,$data[$i]['apply_beginDate'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('H' . $num,$data[$i]['apply_endDate'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('I' . $num,$data[$i]['apply_days'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('J' . $num,$data[$i]['apply_type'] , PHPExcel_Cell_DataType::TYPE_STRING);

            $objActSheet->setCellValueExplicit('K' . $num,$data[$i]['remark'] , PHPExcel_Cell_DataType::TYPE_STRING);

            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('25px');//设置行高  
            $num = $num + 1;  

        }

        //设置行的属性  
        $objActSheet->getRowDimension(1)->setRowHeight('20px');//设置行高  
        //设置列的属性  
        // for ($i=0; $i < 11; $i++) { 
        //     $objActSheet->getColumnDimension(chr(65+$i))->setWidth('20px');// 设置宽度 
        // }
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度    
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("J")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("K")->setWidth(20); // 设置宽度   


        //导出的文件名  
        $outputFileName = iconv('UTF-8', 'gb2312', '请假销假记录.xlsx');

        //文件直接输出到浏览器  
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');  
        
        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

    }

    /** 
    * @Purpose: 
    * 退宿\离职 单人水电费费用计算
    * @Method Name: singleUtilitiesCalc
    * 
    * @Param: $roomMeterArray(
    *       string $jobNumber 工号
    *       string $date 时间戳
    *       string $area_number 区域编号
    *       string $floor_number 宿舍楼编号
    *       string $dorm_floor 宿舍楼层号
    *       string $room_number 房间号
    *       string $room_hotwater 热水表
    *       string $room_coldwater 冷水表
    *       string $room_electric 电表
    *       string $type 0.结算1.退宿2.换宿
    *    )
    * @Author: GeYang
    *
    * @Return: array cost(
    *           cold 冷水费
    *           hot 热水费
    *           electricity 电
    *           capitationCold 均摊冷水费
    *           capitationHot 均摊热水费
    *           capitationElectricity 均摊电费
    *            ) 
    */ 

    public function singleUtilitiesCalc($roomMeterArray)
    {
        /**
         * 查询当月期初电表、冷水表、热水表度数（上月月底）
         * 保存当前条记录
         * 计算冷水费、热水费、电费
         * 计算当前房间每人当月入住天数
         * 拉取当月房间内退宿、离职人员费用
         * 拉取均摊费用单价、热水费单价
         */
        // if
        // (   empty($roomMeterArray['jobNumber']) ||
        //     empty($roomMeterArray['date']) ||
        //     empty($roomMeterArray['area_number']) ||
        //     empty($roomMeterArray['floor_number']) ||
        //     empty($roomMeterArray['dorm_floor']) ||
        //     empty($roomMeterArray['room_number']) ||
        //     empty($roomMeterArray['room_hotwater']) ||
        //     empty($roomMeterArray['room_coldwater']) ||
        //     empty($roomMeterArray['room_electric']) ||
        //     empty($roomMeterArray['type'])
        // )
        if
        (   empty($roomMeterArray['date']) ||
            empty($roomMeterArray['area_number']) ||
            empty($roomMeterArray['floor_number']) ||
            empty($roomMeterArray['dorm_floor']) ||
            empty($roomMeterArray['room_number']) ||
            // empty($roomMeterArray['room_hotwater']) ||
            // empty($roomMeterArray['room_coldwater']) ||
            // empty($roomMeterArray['room_electric']) ||
            empty($roomMeterArray['type'])
        )

        {
            return json_encode(array('res' => '41001', 'msg' => '数据异常', 'data' =>''));
        }
        else
        {

            $sumDays = 0;
            $singleDays = 0;

            // $roomMeterArray['jobNumber'] = $_POST['jobNumber'];
            // $roomMeterArray['date'] = $_POST['date'];
            // $roomMeterArray['area_number'] = $_POST['area_number'];
            // $roomMeterArray['floor_number'] = $_POST['floor_number'];
            // $roomMeterArray['dorm_floor'] = $_POST['dorm_floor'];
            // $roomMeterArray['room_number'] = $_POST['room_number'];
            // $roomMeterArray['room_hotwater'] = $_POST['room_hotwater'];
            // $roomMeterArray['room_coldwater'] = $_POST['room_coldwater'];
            // $roomMeterArray['room_electric'] = $_POST['room_electric'];
            // $roomMeterArray['type'] = $_POST['type'];

            // 查询当月期初电表、冷水表、热水表度数（上月月底）
            //  $lastMonth = date('Y-m-d', strtotime("-1 day",strtotime(date('Y-m-01', strtotime(date('Y-m-d', $roomMeterArray['date']))))));
            
            // $roomMeter = $this->ApplicationForm_model->getRoomMeter($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number'], $lastMonth);
            
            //核算月上月第一天

            $monthBegin = date("Y-m-01", (strtotime("-1 month", strtotime($roomMeterArray['date']))));;
            
            //核算月上月最后一天
            $monthEnd = date("Y-m-t", (strtotime("-1 month", strtotime($roomMeterArray['date']))));

            $roomMeter = $this->ApplicationForm_model->getRoomMeter($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number'], $monthBegin, $monthEnd, 1);//type:1 非公共房间
            $roomMeter = json_decode($roomMeter);

            if($roomMeter->res === '40000')
            {
                $roomMeter = $roomMeter->data;
            }

            // 保存当前条房间水电表记录
            $roomMeterSaveResult = $this->ApplicationForm_model->saveRoomMeter($roomMeterArray['date'], $roomMeterArray['jobNumber'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['room_hotwater'], $roomMeterArray['room_coldwater'], $roomMeterArray['room_electric'], 1);

            // 拉取公摊费用系数、热水费单价
            // $month = date('m',$roomMeterArray['date']);
            // $month = $month - 1;
            // $year = date('Y',$roomMeterArray['date']);

            // $capitation = $this->ApplicationForm_model->getCapitation($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $year, $month);

            $capitation = $this->ApplicationForm_model->getCapitation($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['date']);

            $capitation = json_decode($capitation);

            if($capitation->res === '40000')
            {
                $capitation = $capitation->data;
            }

            // 计算冷水费、热水费、电费
            // 冷水费 = ( 当前水表度数 - 月初水表度数 ) * 单价 7
            $coldWaterFee = ( $roomMeterArray['room_coldwater'] - $roomMeter->room_coldwater ) * 7;

            // 电费 = ( 当前电表度数 - 月初电表度数 ) * 单价 1
            $electricFee = ( $roomMeterArray['room_electric'] - $roomMeter->room_electric ) * 1;

            // 热水费 = ( 当前热水表度数 - 月初热水表度数 ) * 单价
            $hotWaterFee = ( $roomMeterArray['room_hotwater'] - $roomMeter->room_hotwater ) * $capitation->hotwater_price;

            // 计算当前房间人天

            // 拉取房间当前入住人员
            $roomInfo = $this->ApplicationForm_model->room_livepeople($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number']);
    
            $roomInfo = json_decode($roomInfo);
            $roomInfo = $roomInfo->data;
            // 计算每个人入住天数

            // for ($i=0; $i < count($roomInfo); $i++) 
            // { 
                
            //     $days = $this->calcSignleDay($roomInfo[$i]->jobNumber, $roomMeterArray['date']);
            //     $sumDays += $days; // 房间总人天 

            //     if( $roomInfo[$i]->jobNumber == $roomMeterArray['jobNumber'] )
            //     {

            //         $singleDays = $days;

            //     }
            //     $days = 0;

            // }
            for ($i=0; $i < count($roomInfo); $i++) 
            { 
                $days = $this->calcSignleDay($roomInfo[$i]->jobNumber, $roomInfo[$i]->idNumber, $roomMeterArray['date']);
                $sumDays += $days; // 房间总人天 

                if( $roomInfo[$i]->jobNumber == $roomMeterArray['jobNumber'] OR $roomInfo[$i]->idNumber == $roomMeterArray['idNumber'])
                {
                    $singleDays = $days;
                }
                $days = 0;
            }

            // foreach ($roomInfo as $roomInfoRow) 
            // {
            //     $days = $this->calcSignleDay($roomInfoRow->jobNumber, $roomMeterArray['date']);

            //     $sumDays += $days; // 房间总人天

            //     if( $roomInfoRow->jobNumber == $roomMeterArray['jobNumber'] )
            //     {

            //         $singleDays = $days;

            //     }
            
            //     $days = 0;

            // }

            // 保存人天表
            // $this->savemanday($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['jobNumber'], $singleDays, $roomMeterArray['type'], $roomMeterArray['date']);
            // 保存人天表
            $this->savemanday($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['jobNumber'], $roomMeterArray['idNumber'], $singleDays, $roomMeterArray['type'], $roomMeterArray['date']);

            // 拉取退宿、换宿人员费用
            $pastCost = $this->personCost($roomMeterArray['area_number'], 
            $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'],  $roomMeterArray['date']);

            // 计算费用

            // 冷水/热水/电费 = ( 总费用 - 离职/退宿人员费用 ) / 总人天 * 个人人天
            $cold = ( $coldWaterFee - $pastCost['cost_coldwater'] ) / $sumDays *  $singleDays;

            $electricity = ( $electricFee - $pastCost['cost_electric'] ) / $sumDays *  $singleDays;

            $hot = ( $hotWaterFee - $pastCost['cost_hotwater'] ) / $sumDays *  $singleDays;
            
            // 均摊费用 = 均摊费用单价 * 个人人天
            $capitationCold = $capitation->public_coldwater * $singleDays;
            $capitationHot = $capitation->public_hotwater * $singleDays;
            $capitationElectricity = $capitation->public_electric * $singleDays;
            
            // 总费用
            $costSum = $cold + $hot + $electricity + $capitationCold + $capitationHot + $capitationElectricity;

            $result = array(
                'cold' => $cold,
                'hot' => $hot,
                'electricity' => $electricity,
                'capitationCold' => $capitationCold,
                'capitationHot' => $capitationHot,
                'capitationElectricity' => $capitationElectricity,
                'costSum' => $costSum,
                'singleDays' => $singleDays
            );

            // // 保存个人费用
            // $singleCost = array(
            //     'cost_date' => $roomMeterArray['date'],
            //     'area_number' => $roomMeterArray['area_number'],
            //     'floor_number' => $roomMeterArray['floor_number'],
            //     'dorm_floor' => $roomMeterArray['dorm_floor'],
            //     'room_number' => $roomMeterArray['room_number'],
            //     'jobNumber' => $roomMeterArray['jobNumber'],
            //     // 'name' => 'zxy',
            //     'month_dayall' => $singleDays,
            //     'person_hotwater' => $hot, 
            //     'person_coldwater' => $cold, 
            //     'person_electric' => $electricity,
            //     'public_hotwater' => $capitationHot, 
            //     'public_coldwater' => $capitationCold,
            //     'public_electric' => $capitationElectricity,
            //     'costAll' => $costSum, 
            //     'type' => $roomMeterArray['type'], 
            // );
            // 保存个人费用
            $singleCost = array(
                'cost_date' => $roomMeterArray['date'],
                'area_number' => $roomMeterArray['area_number'],
                'floor_number' => $roomMeterArray['floor_number'],
                'dorm_floor' => $roomMeterArray['dorm_floor'],
                'room_number' => $roomMeterArray['room_number'],
                'jobNumber' => $roomMeterArray['jobNumber'],
                'idNumber' => $roomMeterArray['idNumber'],
                'month_dayall' => $singleDays,
                'person_hotwater' => $hot, 
                'person_coldwater' => $cold, 
                'person_electric' => $electricity,
                'public_hotwater' => $capitationHot, 
                'public_coldwater' => $capitationCold,
                'public_electric' => $capitationElectricity,
                'costAll' => $costSum, 
                'type' => $roomMeterArray['type'], 
            );
            $saveSingleFee = $this->ApplicationForm_model->saveSingleFee($singleCost);
        }

        if($result)
        {
            $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $result);
            $result = json_encode($return_su);
        }
        else
        {
            $return_fa = array('res' => '40001', 'msg' => '', 'data' => '');
            $result = json_encode($return_fa);
        }
        return $result;

    }

    /** 
    * @Purpose: 
    * 月底热水费单价计算
    * @Method Name: calcHotWaterUnitPrice
    * 
    * @Param
    *       string $date 时间戳
    *       string $area_number 区域编号
    *       string $floor_number 宿舍楼编号
    *    
    * @Author: GeYang
    *
    * @Return: array cost(
    *       string $area_number 区域编号
    *       string $floor_number 宿舍楼编号
    *       number $unitPrice
    *            ) 
    */
    // public function calcHotWaterUnitPrice($date, $area_number, $floor_number)
    // {

    //     if( empty($date) || empty($area_number) || empty($floor_number) )
    //     {

    //         return json_encode(array('res' => '41001', 'msg' => '数据异常', 'data' =>''));

    //     }

    //     // 查询当前月
    //     $beginDate = date('Y-m-01', strtotime($date));
    //     $endDate =  date('Y-m-d', strtotime("$beginDate + 1 month -1 day"));
    //     $currentMonth = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, 0, $beginDate, $endDate);
    //     $currentMonth = json_decode($currentMonth);
    //     if( $currentMonth->res === '40000' )
    //     {
    //         $currentMonth = $currentMonth->data;
    //     }

    //     // 查询上个月
    //     $endDate = date('Y-m-d', strtotime(" $beginDate -1 day "));
    //     $beginDate = date('Y-m-01' , strtotime($endDate));
        
    //     $lastMonth = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, '', $beginDate, $endDate);
        
    //     $lastMonth = json_decode($lastMonth);
    //     if( $lastMonth->res === '40000' )
    //     {

    //         $lastMonth = $lastMonth->data;

    //     }

    //     // 每吨热水费=｛空气能用电（每度*0.9元）+空气能用水（每吨*7元）}/空气能用水														
    //     $hotWaterUnitPrice = ( ( $currentMonth->airElectricity -  $lastMonth->airElectricity ) * 0.9 + ( $currentMonth->airWater -  $lastMonth->airWater ) ) * 7 / ( $currentMonth->airWater -  $lastMonth->airWater );

    //     $result = array(
    //         'area_number' => $area_number,
    //         'floor_number' => $floor_number,
    //         'unitPrice' => $hotWaterUnitPrice,
    //     );

    //     return $result;

    // }

    /**
     * @description: 
     * @param {date, area_number, floor_number}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function calcHotWaterUnitPrice($date, $area_number, $floor_number)
    {

        if( empty($date))
        {

            return json_encode(array('res' => '41001', 'msg' => '数据异常', 'data' =>''));

        }

        // 查询当前月
        $beginDate = date('Y-m-01', strtotime($date));
        $endDate =  date('Y-m-t', strtotime($date));
        $currentMonth = $this->ApplicationForm_model->searchFloorMeter('1585', '1', '3', $beginDate, $endDate);
        $currentMonth = json_decode($currentMonth);
        if( $currentMonth->res === '40000' )
        {
            $currentMonth = $currentMonth->data;
        }

        // 查询上个月
        $endDate = date('Y-m-d', strtotime(" $beginDate -1 day "));
        $beginDate = date('Y-m-01' , strtotime($endDate));
        
        $lastMonth = $this->ApplicationForm_model->searchFloorMeter('1585', '1', '3', $beginDate, $endDate);
        
        $lastMonth = json_decode($lastMonth);
        if( $lastMonth->res === '40000' )
        {

            $lastMonth = $lastMonth->data;

        }

        // 每吨热水费=｛空气能用电（每度*0.9元）+空气能用水（每吨*7元）}/空气能用水		
        if($currentMonth->airWater -  $lastMonth->airWater != 0)
        {
            $hotWaterUnitPrice = ( ( $currentMonth->airElectricity -  $lastMonth->airElectricity ) * 0.9 + ( $currentMonth->airWater -  $lastMonth->airWater )  * 7 ) / ( $currentMonth->airWater -  $lastMonth->airWater );
        }

        $result = array(
            'area_number' => $area_number,
            'floor_number' => $floor_number,
            'unitPrice' => $hotWaterUnitPrice,
        );

        return $result;

    }

    /** 
    * @Purpose: 
    * 月底公摊热水费计算
    * @Method Name: capitationFeeHotPrice
    * 
    * @Param
    *       string area_number 时间戳
    *       string floor_number 区域编号
    *       number floorSumDays 楼层总人天
    *       number unitPrice 热水单价
    *       date   date 时间
    * @Author: GeYang
    *
    * @Return: array cost(
    *       string area_number 区域编号
    *       string floor_number 宿舍楼编号
    *       number capitationFeeHotPrice 公摊热水费单价
    *            ) 
    */
    public function capitationFeeHotPrice($area_number, $floor_number, $floorSumDays, $date, $unitPrice)
    { 

        $sumHotMeter = 0;
        // （ 空气能总水表-(各楼层非公共房间消耗热水度数总和）* 每吨热水费 ）/ 总人天 * 个人人天

        //调取当月空气能总水表
        $beginDate = date('Y-m-01', strtotime($date));
        $endDate =  date('Y-m-t', strtotime($date));
        
        $currentMonth = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, 6, $beginDate, $endDate);
        $currentMonth = json_decode($currentMonth);

        if( $currentMonth->res == '40000' )
        {
            $currentMonth = $currentMonth->data;
        }

        // 调取上月空气能总水表

        $lastEndDate = date('Y-m-d', strtotime(" $beginDate -1 day "));
        $lastBeginDate = date('Y-m-01' , strtotime($lastEndDate));
        
        $lastMonth = $this->ApplicationForm_model->searchFloorMeter($area_number, $floor_number, 6, $lastBeginDate, $lastEndDate);
        
        $lastMonth = json_decode($lastMonth);

        if( $lastMonth->res === '40000' )
        {

            $lastMonth = $lastMonth->data;

        }

        // 调取当月非公共房间热水消耗总和
        for ($i=0; $i < 4; $i++) 
        { 
            
            $dorm_floor = $i+3;
            $sumRoomMeter = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $beginDate, $endDate , 1);
            $sumRoomMeter = json_decode($sumRoomMeter);

            if($sumRoomMeter->res = '40000')
            {
                $sumRoomMeter = $sumRoomMeter->data;
            }

            $sumHotMeter += $sumRoomMeter->sumWaterHot;
        }

        // 调取上月非公共房间热水消耗总和

        for ($i=0; $i < 4; $i++) 
        { 
            
            $dorm_floor = $i+3;
            $sumRoomMeter = $this->ApplicationForm_model->SumRoomMeter($area_number, $floor_number, $dorm_floor, $lastBeginDate, $lastEndDate , 1);
            $sumRoomMeter = json_decode($sumRoomMeter);

            if($sumRoomMeter->res = '40000')
            {
                $sumRoomMeter = $sumRoomMeter->data;
            }

            $sumHotMeter -= $sumRoomMeter->sumWaterHot;
        }

            $capitationFeeHotPrice = ( ($currentMonth->airWater - $lastMonth->airWater - $sumHotMeter) * $unitPrice ) / $floorSumDays->data;

            if($capitationFeeHotPrice<0)
            {
                $capitationFeeHotPrice = 0;
            }

            $result = array(
                'area_number' => $area_number,
                'floor_number' => $floor_number,
                'capitationFeeHotPrice' => $capitationFeeHotPrice
            );


    }

    /**
     * @description:A/B栋 、333上月每天需负担冷水费 
     * @param {area_number, floor_number, date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function everydayColdWater($area_number, $floor_number, $date)
    {
        $coldWater = $this->ApplicationForm_model->lastMonth($area_number, $floor_number, $date);

        $everydayColdWater = $coldWater['data']->person_coldwater / $coldWater['data']->month_dayall;

        if($everydayColdWater->num_rows()>0)
        {
            return json_encode(array('res' => '40000', 'msg' => '操作成功', 'data'=> $everydayColdWater));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '操作失败', 'data'=> ''));
        }
    }

    /** 
    * @Purpose: 
    * 月底公摊冷水费计算(4号楼/178)
    * @Method Name: capitationFeeHotPrice
    * 
    * @Param
    *       string area_number 时间戳
    *       string floor_number 区域编号
    *       number floorSumDays 楼层总人天
    *       number unitPrice 热水单价
    *       date   date 时间
    * @Author: GeYang
    *
    * @Return: array cost(
    *       string area_number 区域编号
    *       string floor_number 宿舍楼编号
    *       number capitationFeeHotPrice 公摊热水费单价
    *            ) 
    */

    // public function calcFloorElectricityCapitation($date, $area_number, $floor_number, $dorm_floor)
    // {


    //     /**
    //      * 各楼层电表均摊费用
    //      * 公共电费 = 总电表 - 宿舍用电
    //      */
        
    //     // 取当前月宿舍用电总和

    //     // 取上月宿舍用电总和

    //     // 取当月总电表度数

    //     // 取上月总电表度数


    // }

    /**
     * @description: 个人费用计算
     * @param {jobNumber, date, area_number, floor_number, dorm_floor, room_number, room_hotwater, room_coldwater, room_electric, type}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function singleCalc()
    {
        $jobNumber = $_POST['jobNumber'];           //工号
        $date = $_POST['date'];                     //日期
        $area_number = $_POST['area_number'];       //区域编号
        $floor_number = $_POST['floor_number'];     //宿舍楼编号
        $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
        $room_number = $_POST['room_number'];       //房间号
        $room_hotwater = $_POST['room_hotwater'];   //热水表
        $room_coldwater = $_POST['room_coldwater']; //冷水表
        $room_electric = $_POST['room_electric'];   // 电表
        $type = $_POST['type'];                     //0.月底结算1.退宿2.调宿

        // $jobNumber = "B1181";
        // $date = "2020-11-19";
        // $area_number = "1585";
        // $floor_number = "1";
        // $dorm_floor = "4";
        // $room_number = "450";
        // $room_hotwater = "100";
        // $room_coldwater = "100";
        // $room_electric = "100";
        // $type = "2";

        $roomMeterArray = array(
            'jobNumber' => $jobNumber, 
            'date' => $date,
            'area_number' => $area_number, 
            'floor_number' => $floor_number, 
            'dorm_floor' => $dorm_floor,
            'room_number' => $room_number, 
            'room_hotwater' => $room_hotwater, 
            'room_coldwater' => $room_coldwater, 
            'room_electric' => $room_electric, 
            'type' => $type
        );

        $result_json = $this->singleUtilitiesCalc($roomMeterArray);

        echo $result_json;

        return 0;
    }

    /**
     * @description: 导入楼层水电表
     * @param {*}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function floorMeterImport()
    {   

        $result = array();

        // 将文件保存至服务器中
        // if (isset($_POST['upload'])) { 
        //     var_dump($_FILES);
        //     // echo $_FILES;
        //     if(count($_FILES) === 0){
        //         echo json_encode(array('res' => '40012', 'msg' => '无法获取文件', 'data' => ''));
        //         return;
        //     }else {
        //         // $time = time();
        //         $fileName = $_FILES['upfile']['name'];
        //         move_uploaded_file($_FILES['upfile']['tmp_name'], 'floorIn/'.$fileName.'.xls');
        //         // echo "导入成功&$time";
        //     }
        // }
        if(is_uploaded_file($_FILES['file']['tmp_name']))
        {  
            $root_dir ="floorIn/";  
            $filename =  $_FILES['file']['name'];
            // $filename =  $_FILES['file']['name'];
            // $uploadfile = $root_dir . $filename; 
            // move_uploaded_file($_FILES['file']['tmp_name'], $uploadfile);
            $uploadfile = iconv("UTF-8", "GB2312", $root_dir.$filename);
            move_uploaded_file($_FILES['file']['tmp_name'], $uploadfile);
        }

        // 将数据保存至数据库中
        // $fileName = $_POST['fileName'];
        // $fileName = iconv("UTF-8", "GB2312", $_FILES['file']['name']);
        // $uploadfile ='C:\xampp\htdocs\dorm_manage_DEV\floorIn\\'.$fileName;
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        if(!$objReader->canRead($uploadfile)){
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        }
        // $objPHPExcel = $objReader->load($file,$encode='utf-8');
        // $objReader = PHPExcel_IOFactory::createReader('Excel2007');/*Excel5 for 2003 excel2007 for 2007*/  
        $objPHPExcel = $objReader->load($uploadfile); //Excel 路径  
        $sheet = $objPHPExcel->getSheet(0);
        $objWorksheet = $objPHPExcel->getActiveSheet();          
        $highestRow = $objWorksheet->getHighestRow();   // 取得总行数       
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
        for ($row = 2,$count=0;$row <= $highestRow;$row++,$count++) {
        
            $strs=array(); 

            //注意highestColumnIndex的列数索引从0开始  
            for ($col = 0;$col < $highestColumnIndex;$col++)
            {  
                $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); 
            }

            $_result = $this->ApplicationForm_model->insertFloorMeter($strs);
            
            array_push($result, $_result);

            unset($_result);

        }

        echo json_encode(array('res' => '40000', 'data' => $result, 'msg' => '导入成功'));

        return 0;

    }

    /**
     * @description: 导入房间水电表
     * @param {*}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function roomMeterImport()
    {   

        $result = array();
        // 将文件保存至服务器中
        // if (isset($_POST['file'])) { 
        //     var_dump($_FILES);
        //     // echo $_FILES;
        //     if(count($_FILES) === 0){
        //         echo json_encode(array('res' => '40012', 'msg' => '无法获取文件', 'data' => ''));
        //         return;
        //     }else {
        //         // $time = time();
        //         $fileName = $_FILES['file']['name'];
        //         move_uploaded_file($_FILES['file']['tmp_name'], 'roomIn/'.$fileName.'xls');
        //         echo "导入成功&$fileName ";
        //     }
        // }
        if(is_uploaded_file($_FILES['file']['tmp_name']))
        {  
            $root_dir ="roomIn/";  
            $filename =  $_FILES['file']['name'];
            // $filename =  $_FILES['file']['name'];
            // $uploadfile = $root_dir . $filename; 
            $uploadfile = iconv("UTF-8", "GB2312", $root_dir.$filename);
            move_uploaded_file($_FILES['file']['tmp_name'], $uploadfile);
        }

        // 将数据保存至数据库中
        // $fileName = $_FILES['file']['name'];
        // $uploadfile ='C:\xampp\htdocs\dorm_manage_DEV\roomIn\\'.$fileName;
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        if(!$objReader->canRead($uploadfile)){
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        }
        // $objPHPExcel = $objReader->load($file,$encode='utf-8');
        // $objReader = PHPExcel_IOFactory::createReader('Excel2007');/*Excel5 for 2003 excel2007 for 2007*/  
        $objPHPExcel = $objReader->load($uploadfile); //Excel 路径  
        $sheet = $objPHPExcel->getSheet(0);
        $objWorksheet = $objPHPExcel->getActiveSheet();          
        $highestRow = $objWorksheet->getHighestRow();   // 取得总行数       
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
        for ($row = 2,$count=0;$row <= $highestRow;$row++,$count++) {
        
            $strs=array(); 

            //注意highestColumnIndex的列数索引从0开始  
            for ($col = 0; $col < $highestColumnIndex; $col++)
            {  
                $strs[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); 
            }

            $_result = $this->ApplicationForm_model->insertRoomMeter($strs);
            
            
            array_push($result, $_result);

            unset($_result);

        }

        echo json_encode(array('res' => '40000', 'data' => $result, 'msg' => '导入成功'));

        return 0;

    }

    /**
     * @description: 计算人天后存入自建表
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    //取出所有人员
    public function monthCalc_liveDays($date)
    {
        //取出所有在住人员
        $livePeople = $this->ApplicationForm_model->livePeople();
        $livepeople = json_decode($livePeople);
        $date = date('Y-m-d',strtotime($date));
        $type = 0;
        for($i=0; $i<count($livepeople->data); $i++)
        { 
            //计算每人人天
            $liveDays = $this->calcSignleDay($livepeople->data[$i]->jobNumber, $date);

            //人天存入人天表
            $savemanday = $this->savemanday($livepeople->data[$i]->area_number, $livepeople->data[$i]->floor_number, $livepeople->data[$i]->dorm_floor, $livepeople->data[$i]->room_number, $livepeople->data[$i]->jobNumber, $liveDays, $type, $date);
        }
        // if($savemanday)
        // {
        //     return json_decode(array('res' => '40000', 'msg' => '操作成功', 'data' => ''));
        // }
        // else
        // {
        //     return json_decode(array('res' => '40000', 'msg' => '操作失败', 'data' => ''));
        // }
    }

    /**
     * @description:公摊费用计算并存入表 
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function monthCalc_publicCost($date)
    {
        $arr_4 = [];
        $arr_AB = [];
        $arr_333 = [];
        $arr_public = [];
        $type = 0; //0:结算

        //计算热水费单价
        $unitPrice = $this->calcHotWaterUnitPrice($date, '1585', '1');

        $beginDate = date("Y-m-01",(strtotime($date)));
        $endDate = date("Y-m-t",(strtotime($date)));

        //1585 4号楼费用
        for($i=3; $i<=6; $i++)
        {
            //公摊电费
            $publicElectricty_4 = $this->publicElectricty('1585', '1', $i, $beginDate, $endDate);
            $publicElectricty_4 = json_decode($publicElectricty_4);

            //楼层总人天
            // $floorSumDays = $this->floor_manday('1585', '1', $i, $date);
            // $floorSumDays = json_decode($floorSumDays);
            
            $floorSumDays = $this->dorm_manaday('1585', '1', $date);
            $floorSumDays = json_decode($floorSumDays);

            //1585公摊热水费
            $publicHotWater = $this->capitationFeeHotPrice('1585','1', $floorSumDays, $date, $unitPrice['unitPrice']);

            //公共冷水费(4号楼)
            $publicColdWater_4 = $this->publicColdWater_4('1585', '1', $i, $beginDate, $endDate);
            $publicColdWater_4 = json_decode($publicColdWater_4);

            //数据存入表
            $arr_4 = array(
                'date' => $date,
                'area_number' => '1585',
                'floor_number' => '1',
                'dorm_floor' => $i,
                'hotwater_price' => $unitPrice['unitPrice'],
                'public_hotwater' => $publicHotWater['capitationFeeHotPrice'],
                'public_electric' => $publicElectricty_4->data,
                'public_coldwater' => $publicColdWater_4->data
            );
            array_push($arr_public, $arr_4);
        }

        //1585 AB栋费用
        //公摊电费
        $publicElectricty_AB = $this->publicElectricty('1585', '2', '1', $beginDate, $endDate);
        $publicElectricty_AB = json_decode($publicElectricty_AB);

        //冷水费
        // $ColdWater_AB = $this->ColdWater_333_AB('1585', '2', '1', $beginDate, $endDate);
        // $ColdWater_AB = json_decode($ColdWater_AB);

        //数据存入表
        $arr_AB = array(
            'date' => $date,
            'area_number' => '1585',
            'floor_number' => '2',
            'dorm_floor' => '1',
            'hotwater_price' => '',
            'public_hotwater' => '',
            'public_electric' => $publicElectricty_AB->data,
            // 'public_coldwater' => $ColdWater_AB->data 
            'public_coldwater' => ''
        );
        array_push($arr_public, $arr_AB);

        //333 333号费用
        for($j=1; $j<=3; $j++)
        {
            //公摊电费
            $publicElectricty_333 = $this->publicElectricty('333', '3', $j, $beginDate, $endDate);
            $publicElectricty_333 = json_decode($publicElectricty_333);

            // //冷水费
            // $ColdWater_333 = $this->ColdWater_333_AB('333', '3', $j, $beginDate, $endDate);
            // $ColdWater_333 = json_decode($ColdWater_333);

            //公共热水费
            $public_hotWater_333 = $this->public_hotWater_333('333', '3', $j, $beginDate, $endDate);
            $public_hotWater_333 = json_decode($public_hotWater_333);

            //数据存入表
            $arr_333 = array(
                'date' => $date,
                'area_number' => '333',
                'floor_number' => '3',
                'dorm_floor' => $j,
                'hotwater_price' => '',
                'public_hotwater' => $public_hotWater_333->data,
                'public_electric' => $publicElectricty_333->data,
                // 'public_coldwater' => $ColdWater_333->data
                'public_coldwater' => ''
            );
            array_push($arr_public, $arr_333);
        }

        //178 178号费用
        for($x=1; $x<=2; $x++)
        {
            //公摊电费
            $publicElectricty_178 = $this->publicElectricty('178', '4', $x, $beginDate, $endDate);
            $publicElectricty_178 = json_decode($publicElectricty_178);

            //公共冷水费
            $publicColdWater_178 = $this->publicColdWater_178('178', '4', $x, $beginDate, $endDate);
            $publicColdWater_178 = json_decode($publicColdWater_178);

            //数据存入表
            $arr_178 = array(
                'date' => $date,
                'area_number' => '178',
                'floor_number' => '4',
                'dorm_floor' => $x,
                'hotwater_price' => '',
                'public_hotwater' => '',
                'public_electric' => $publicElectricty_178->data,
                'public_coldwater' => $publicColdWater_178->data
            );
            array_push($arr_public, $arr_178);
        }

        //数据存入表中
        $query_public = $this->ApplicationForm_model->public_hydropower($arr_public);
        $query_public = json_decode($query_public);

        if($query_public->res == '40000')
        {
            return json_encode(array('res' => '40000', 'msg' => '操作成功', 'data' => $arr_public));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '操作失败', 'data' => ''));
        }
    }


    /**
     * @description: 计算每个房间的冷水、热水、电费,并且按照区域+宿舍楼+楼层+房间作为索引存放对象数组中
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function roomCost($date)
    {
        $arr_room = [];

        //核算月上月第一天
        $last_monthFirst = date("Y-m-01", (strtotime("-1 month", strtotime($date))));

        //核算月上月最后一天
        $last_monthEnd = date("Y-m-t", (strtotime("-1 month", strtotime($date))));

        //核算月第一天
        $monthFirst = date("Y-m-01",(strtotime($date)));

        //核算月最后一天
        $monthEnd = date("Y-m-t",(strtotime($date)));

        //1585 4号所有房间费用
        //取4号楼所有房间
        $room_4 = $this->Room_manage->dormRoom('1585', '1');
        $room_4 = json_decode($room_4);

        $hotWaterPrice = $this->ApplicationForm_model->hotwater_price('1585', '1', $date);
        $hotWaterPrice = json_decode($hotWaterPrice);
        $hotWaterPrice = $hotWaterPrice->data->hotwater_price;

        for($i=0; $i<count($room_4->data); $i++)
        {   
            //上月度数
            // $type = 0;
            $lastMonth_4 = $this->ApplicationForm_model->electric('1585', '1', $room_4->data[$i]->dorm_floor, $room_4->data[$i]->room_number, $last_monthFirst, $last_monthEnd);
            $lastMonth_4 = json_decode($lastMonth_4);

            //本月度数
            $month_4  = $this->ApplicationForm_model->electric('1585', '1',  $room_4->data[$i]->dorm_floor, $room_4->data[$i]->room_number, $monthFirst, $monthEnd);
            $month_4  = json_decode($month_4 );

            //上月热水表度数
            if(empty($lastMonth_4->data->room_hotwater))
            {
                $roomLastMonth_hotWater_4 = 0;
            }
            else
            {
                $roomLastMonth_hotWater_4 = $lastMonth_4->data->room_hotwater;
            }

            //本月热水表度数
            if(empty($month_4->data->room_hotwater))
            {
                $roomMonth_hotWater_4 = 0;
            }
            else
            {
                $roomMonth_hotWater_4 = $month_4->data->room_hotwater;
            }

            //本月实际热水度数 = 本月度数 - 上月度数
            $monthHotWater = $roomMonth_hotWater_4 -  $roomLastMonth_hotWater_4;
            
            //上月冷水表度数
            if(empty($lastMonth_4->data->room_coldwater))
            {
                $roomLastMonth_coldWater_4 = 0;
            }
            else
            {
                $roomLastMonth_coldWater_4 = $lastMonth_4->data->room_coldwater;
            }

            //本月冷水表度数
            if(empty($month_4->data->room_coldwater))
            {
                $roomMonth_coldwater_4 = 0;
            }
            else
            {
                $roomMonth_coldwater_4 = $month_4->data->room_coldwater;
            }

            //本月实际冷水表度数
            $monthColdWater_4 = $roomMonth_coldwater_4 -  $roomLastMonth_coldWater_4;

            //上月电表度数
            if(empty($lastMonth_4->data->room_electric))
            {
                $roomLastMonth_electric_4 = 0;
            }
            else
            {
                $roomLastMonth_electric_4 = $lastMonth_4->data->room_electric;
            }

            //本月电表度数
            if(empty($month_4->data->room_electric))
            {
                $roomMonth_electric_4 = 0;
            }
            else
            {
                $roomMonth_electric_4  = $month_4->data->room_electric;
            }

            //本月实际电表度数
            $monthelectric_4 = $roomMonth_electric_4 -  $roomLastMonth_electric_4;

            // if(empty($hotWaterPrice->data->hotwater_price))
            // {
            //     $hotWaterPrice = 0;
            // }
            // else
            // {
            //     $hotWaterPrice = $hotWaterPrice->data->hotwater_price;
            // }

            if($monthHotWater == 0 || $hotWaterPrice == 0)
            {
                $monthHotWater_cost = 0;
            }
            else
            {
                //房间热水费 = 本月实际热水表度数 * 热水费单价
                $monthHotWater_cost = $monthHotWater * $hotWaterPrice;
            }

            if($monthColdWater_4 == 0)
            {
                $monthColdWater_cost_4 = 0;
            }
            else
            {
                //房间冷水费 = 本月实际冷水表度数 * 7
                $monthColdWater_cost_4 = $monthColdWater_4 * 7;

            }

            //房间电费 = 本月实际电表度数 * 1
            $monthelectric_cost_4 = $monthelectric_4;
            
            $arr_room_4 = array(
                'area_number' => '1585',
                'floor_number' => '1',
                'dorm_floor' => $room_4->data[$i]->dorm_floor,
                'room_number' => $room_4->data[$i]->room_number,
                'room_hotwater' => $monthHotWater_cost,
                'room_coldwater' => $monthColdWater_cost_4,
                'room_electric' => $monthelectric_cost_4
            );
            array_push($arr_room, $arr_room_4);
        }
        

        //1585 AB栋所有费用
        //取AB栋所有房间
        $room_AB = $this->Room_manage->dormRoom('1585', '2');
        $room_AB = json_decode($room_AB);

        // //宿舍总人天
        // $dorm_manday_AB = $this->ApplicationForm_model->SumDays('1585', '2', $monthFirst, $monthEnd);
        // $dorm_manday_AB = json_decode($dorm_manday_AB);

        //取冷水费
        $coldWater_AB = $this->ColdWater_333_AB('1585', '2', '1', $monthFirst, $monthEnd);
        $ColdWater_AB = json_decode($coldWater_AB);

        //冷水费每人天
        $monthColdWater_cost_AB = $ColdWater_AB->data;

        for($j=0; $j<count($room_AB->data); $j++)
        {   
            //本月实际度数 = 本月度数 - 上月度数

            //上月度数
            $lastMonth_AB = $this->ApplicationForm_model->electric('1585', '2', $room_AB->data[$j]->dorm_floor, $room_AB->data[$j]->room_number, $last_monthFirst, $last_monthEnd);
            $lastMonth_AB = json_decode($lastMonth_AB);

            //本月度数
            $month_AB = $this->ApplicationForm_model->electric('1585', '2',  $room_AB->data[$j]->dorm_floor, $room_AB->data[$j]->room_number, $monthFirst, $monthEnd);
            $month_AB = json_decode($month_AB);

            //本月实际电表度数
            // $month_electric = $month_AB->data->room_electric;
            // $last_electric = $lastMonth_AB->data->room_electric;

            // $monthelectric_AB = $month_electric - $last_electric;
            $monthelectric_AB = $month_AB->data->room_electric - $lastMonth_AB->data->room_electric;


            //房间总人天
            $manday_AB = $this->ApplicationForm_model->room_Manday('1585', '2', $room_AB->data[$j]->dorm_floor, $room_AB->data[$j]->room_number, $monthFirst, $monthEnd);
            $manday_AB = json_decode($manday_AB); 

            // //取冷水费
            // $coldWater_AB = $this->ColdWater_333_AB('1585', '2', $room_AB->data[$j]->dorm_floor, $monthFirst, $monthEnd);
            // $ColdWater_AB = json_decode($coldWater_AB);

            //取洗衣机费用
            $washingMachine = $this->ApplicationForm_model->searchFloorMeter('1585', '2', $room_AB->data[$j]->dorm_floor, $monthFirst, $monthEnd);
            $washingMachine = json_decode($washingMachine);
            
            //房间总人天
            if(empty($manday_AB->data->sumliveDays))
            {
                $sumliveDays = 0;
            }
            else
            {
                $sumliveDays = $manday_AB->data->sumliveDays;
            }

            //房间电费 = 本月实际电表度数 * 1
            $monthelectric_cost_AB = $monthelectric_AB;

            // //房间冷水费 = 冷水费 * 房间总人天
            // $monthColdWater_cost_AB  = $ColdWater_AB->data * $sumliveDays * 7;
            
            $arr_room_AB = array(
                'area_number' => '1585',
                'floor_number' => '2',
                'dorm_floor' => $room_AB->data[$j]->dorm_floor,
                'room_number' => $room_AB->data[$j]->room_number,
                'room_hotwater' => '',
                'room_coldwater' => $monthColdWater_cost_AB,
                'room_electric' => $monthelectric_cost_AB
            );

            array_push($arr_room, $arr_room_AB);
        }
        
        //333 333号所有费用
        //取333号有人入住的所有房间
        $room_333 = $this->Room_manage->dormRoom('333', '3');
        $room_333 = json_decode($room_333);

        for($x=0; $x<count($room_333->data); $x++)
        {   
            //本月实际度数 = 上月度数 - 本月度数

            //上月度数
            $lastMonth_333 = $this->ApplicationForm_model->electric('333', '3', $room_333->data[$x]->dorm_floor, $room_333->data[$x]->room_number, $last_monthFirst, $last_monthEnd);
            $lastMonth_333 = json_decode($lastMonth_333);

            //本月度数
            $month_333 = $this->ApplicationForm_model->electric('333', '3',  $room_333->data[$x]->dorm_floor, $room_333->data[$x]->room_number, $monthFirst, $monthEnd);
            $month_333 = json_decode($month_333);


            //本月实际电表度数
            $monthelectric_333 = $month_333->data->room_electric - $lastMonth_333->data->room_electric;  

            //房间总人天
            // $manday_333 = $this->ApplicationForm_model->room_Manday('333', '3', $room_333->data[$x]->dorm_floor, $room_333->data[$x]->room_number, $monthFirst, $monthEnd);
            // $manday_333 = json_decode($manday_333); 

            //楼层总人天
            $manday_333 = $this->ApplicationForm_model->SumFloorDays('333', '3', $room_333->data[$x]->dorm_floor, $monthFirst, $monthEnd);
            $manday_333 = json_decode($manday_333); 

            //取冷水费
            $coldWater_333 = $this->ColdWater_333_AB('333', '3', $room_333->data[$x]->dorm_floor, $monthFirst, $monthEnd);
            $ColdWater_333 = json_decode($coldWater_333);

            //取洗衣机费用
            $washingMachine = $this->ApplicationForm_model->searchFloorMeter('333','3', $room_333->data[$x]->dorm_floor, $monthFirst, $monthEnd);
            $washingMachine = json_decode($washingMachine);

            // //房间冷水费 = 冷水费 * 房间总人天
            // $monthColdWater_cost_333  = $ColdWater_333->data * $manday_333->data->sumliveDays * 7;

            //冷水费每人天
            $monthColdWater_cost_333  = $ColdWater_333->data;

            //房间电费 = 本月实际电表度数 * 1
            $monthelectric_cost_333 = $monthelectric_333;
            
            $arr_room_333 = array(
                'area_number' => '333',
                'floor_number' => '3',
                'dorm_floor' => $room_333->data[$x]->dorm_floor,
                'room_number' => $room_333->data[$x]->room_number,
                'room_hotwater' => '',
                'room_coldwater' => $monthColdWater_cost_333,
                'room_electric' => $monthelectric_cost_333
            );
            array_push($arr_room, $arr_room_333);
        }

        //178 178号所有费用
        $room_178 = $this->Room_manage->dormRoom('178', '4');
        $room_178 = json_decode($room_178);


        for($y=0; $y<count($room_178->data); $y++)
        {   
            //本月实际度数 = 上月度数 - 本月度数

            //上月度数
            $lastMonth_178 = $this->ApplicationForm_model->electric('178', '4', $room_178->data[$y]->dorm_floor, $room_178->data[$y]->room_number, $last_monthFirst, $last_monthEnd);
            $lastMonth_178  = json_decode($lastMonth_178);

            //本月度数
            $month_178 = $this->ApplicationForm_model->electric('178', '4', $room_178->data[$y]->dorm_floor, $room_178->data[$y]->room_number, $monthFirst, $monthEnd);
            $month_178 = json_decode($month_178);

            //本月实际冷水表度数
            $monthColdWater_178 = $month_178->data->room_coldwater - $lastMonth_178->data->room_coldwater;

            //本月实际电表度数
            $monthelectric_178 = $month_178->data->room_electric - $lastMonth_178->data->room_electric;

            //房间冷水费 = 本月实际冷水表度数 * 7
            $monthColdWater_cost_178 = $monthColdWater_178 * 7;

            //房间电费 = 本月实际电表度数 * 1
            $monthelectric_cost_178 = $monthelectric_178;
            
            $arr_room_178 = array(
                'area_number' => '178',
                'floor_number' => '4',
                'dorm_floor' => $room_178->data[$y]->dorm_floor,
                'room_number' => $room_178->data[$y]->room_number,
                'room_hotwater' => '',
                'room_coldwater' => $monthColdWater_cost_178,
                'room_electric' => $monthelectric_cost_178
            );
            array_push($arr_room, $arr_room_178);
        }

        if(count($arr_room) > 0)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $arr_room));
        }
        else
        {
            return json_encode(array('res' => '40001','msg' => '查询无数据', 'data' => ''));
        }
    }

    /**
     * @description: 
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function roomManday($date)
    {
        $arr = [];
        $beginDate = date("Y-m-01",(strtotime($date)));
        $endDate = date("Y-m-t",(strtotime($date)));

        //取人天表数据
        $room_manday = $this->ApplicationForm_model->roomManday();
        $room_manday = json_decode($room_manday);
        foreach($room_manday as $row)
        {
            array_push($arr, $row->data);
        }
        if(count($arr)>0)
        {
            return json_encode(array('res' => '40000', 'msg' => '查询成功', 'data' => $arr));
        }
        else
        {
            return json_encode(array('res' => '40001', 'msg' => '查询无数据', 'data' => ''));
        }
    }

    /**
     * @description:月底费用结算 
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function monthCalc($date)
    {
        $query = $this->ApplicationForm_model->CostControl();
        $query = json_decode($query);
        if($query->res == '40000')
        {
            return json_encode(array('res'=> '40001', 'msg' => '本月已进行结算，不允许重复结算', 'data' => ''));
        }
        else
        {
            $manday_hotwater = 0;
            $public_hotwater = 0;
            $arr = [];
    
            /**
             * STEP1:
             *  取出所有在住人员
             *  遍历
             *  计算人天后存入自建表
             * STEP2:
             *  计算热水费单价
             *  公摊热水费
             *  公摊电费
             *  公摊冷水费
             *  将以上数据存入表中
             *  并且存放在对象数组中，保证按照对应的取值方法取到(例如热水费按照区域+宿舍楼取值)
             * STEP3:
             * 计算每个房间的冷水、热水、电费,并且按照区域+宿舍楼+楼层+房间作为索引存放对象数组中
             * STEP4:
             * 取出dorm_manday表数据,并且按照房间统计出总人天,并且按照区域+宿舍楼+楼层+房间作为索引存放对象数组中
             * 遍历表dorm_manday中所有数据并按照房间实际消耗的热水、电、冷水费用计算个人费用并保存自建表
            */
            $beginDate = date("Y-m-01",(strtotime($date)));
            $endDate = date("Y-m-t",(strtotime($date)));
    
            //计算人天后存入自建表
            $this->monthCalc_liveDays($date);
    
            //公摊费用计算
            $publicCost = $this->monthCalc_publicCost($date);
            $publicCost = json_decode($publicCost);
    
            //房间费用计算
            $roomCost = $this->roomCost($date);
            $roomCost = json_decode($roomCost);
    
            //取出所有入住人员
            $livePeople = $this->ApplicationForm_model->livePeople();
            $livepeople = json_decode($livePeople);
    
    
            for($i=0; $i<count($livepeople->data); $i++)
            {
                //取本月人天数
                $manday = $this->ApplicationForm_model->month_manday($livepeople->data[$i]->jobNumber, $beginDate, $endDate);
                $manday = json_decode($manday);
                $month_manday = $manday->data->liveDays;
    
    
                //取房间总人天
                $mandayAll = $this->ApplicationForm_model->room_Manday($livepeople->data[$i]->area_number, $livepeople->data[$i]->floor_number, $livepeople->data[$i]->dorm_floor, $livepeople->data[$i]->room_number, $beginDate, $endDate);
                $mandayAll = json_decode($mandayAll);
                if(empty($mandayAll->data->sumliveDays))
                {
                    $roomall = 0;
                }
                else
                {
                    $roomall = $mandayAll->data->sumliveDays;
                }
    
                //取楼层总人天
                $SumFloorDays = $this->ApplicationForm_model->SumFloorDays($livepeople->data[$i]->area_number, $livepeople->data[$i]->floor_number, $livepeople->data[$i]->dorm_floor, $beginDate, $endDate);
                $SumFloorDays = json_decode($SumFloorDays);
                if(empty($SumFloorDays->data->sumLiveDays))
                {
                    $roomall_floor = 0;
                }
                else
                {
                    $roomall_floor =   $SumFloorDays->data->sumLiveDays;
                }
     
    
                //个人费用 = 人天费用 * 个人人天
    
                //取人天费用
    
                if(($livepeople->data[$i]->area_number == '1585' && $livepeople->data[$i]->floor_number == '1') || ($livepeople->data[$i]->area_number == '178'))
                {
                    for($y=0; $y<count($roomCost->data); $y++)
                    {
                        if($roomCost->data[$y]->area_number == $livepeople->data[$i]->area_number && $roomCost->data[$y]->floor_number == $livepeople->data[$i]->floor_number && $roomCost->data[$y]->dorm_floor == $livepeople->data[$i]->dorm_floor && $roomCost->data[$y]->room_number == $livepeople->data[$i]->room_number)
                        {
                            if($roomCost->data[$y]->room_hotwater != '' && $roomall != 0)
                            {
                                $manday_hotwater = $roomCost->data[$y]->room_hotwater / $roomall;
                            }
                            if($roomall != 0)
                            {
    
                                $manday_coldwater = $roomCost->data[$y]->room_coldwater / $roomall;
                                $manday_electric = $roomCost->data[$y]->room_electric / $roomall;
                            }
                        }
                    }
                    //个人费用
                    $person_hotwater = $manday_hotwater * $month_manday;
                    $person_coldwater = $manday_coldwater * $month_manday;
                    $person_electric = $manday_electric * $month_manday;
                }
                else
                {
                    for($y=0; $y<count($roomCost->data); $y++)
                    {
                        if($roomCost->data[$y]->area_number == $livepeople->data[$i]->area_number && $roomCost->data[$y]->floor_number == $livepeople->data[$i]->floor_number && $roomCost->data[$y]->dorm_floor == $livepeople->data[$i]->dorm_floor && $roomCost->data[$y]->room_number == $livepeople->data[$i]->room_number)
                        {
                            $manday_hotwater = 0;
                            $manday_coldwater = $roomCost->data[$y]->room_coldwater;
                            if($roomall != 0)
                            {
                                $manday_electric = $roomCost->data[$y]->room_electric / $roomall;
                            }
                        }
                    }
                    //个人费用
                    $person_hotwater = $manday_hotwater * $month_manday;
                    $person_coldwater = $manday_coldwater * $month_manday;
                    $person_electric = $manday_electric * $month_manday;
                }
    
                ///取公摊费用
                for($z=0; $z<count($publicCost->data); $z++)
                {
                    if($publicCost->data[$z]->area_number == $livepeople->data[$i]->area_number && $publicCost->data[$z]->floor_number == $livepeople->data[$i]->floor_number && $publicCost->data[$z]->dorm_floor == $livepeople->data[$i]->dorm_floor)
                    {
                        if($publicCost->data[$z]->hotwater_price != '')
                        {
                            $public_hotwater = $publicCost->data[$z]->public_hotwater * $month_manday;
                        }

                        if($publicCost->data[$z]->area_number == '333')
                        {
                            $public_hotwater = $publicCost->data[$z]->public_hotwater * $month_manday;
                        }

                        if(empty($publicCost->data[$z]->public_coldwater))
                        {
                            $public_Costcoldwater = 0;
                        }
                        else
                        {
                            $public_Costcoldwater = $publicCost->data[$z]->public_coldwater;
                        }
    
                        $public_coldwater = $public_Costcoldwater * $month_manday;
    
                        if(empty($publicCost->data[$z]->public_electric))
                        {
                            $public_CostElectric = 0;
                        }
                        else
                        {
                            $public_CostElectric = $publicCost->data[$z]->public_electric;
                        }
                        
                        $public_electric = $public_CostElectric * $month_manday;
                    }
                }
    
                $costAll = $person_hotwater + $person_coldwater + $person_electric + $public_hotwater + $public_coldwater + $public_electric;
    
                //数据插入表
                $hydropwerCost = array(
                    'cost_date' => $date,
                    'area_number' => $livepeople->data[$i]->area_number,
                    'floor_number' => $livepeople->data[$i]->floor_number,
                    'dorm_floor' => $livepeople->data[$i]->dorm_floor,
                    'room_number' => $livepeople->data[$i]->room_number,
                    'jobNumber' => $livepeople->data[$i]->jobNumber,
                    'month_dayall' => $month_manday,
                    'person_hotwater' => $person_hotwater,
                    'person_coldwater' => $person_coldwater,
                    'person_electric' => $person_electric,
                    'public_hotwater' => $public_hotwater,
                    'public_coldwater' => $public_coldwater,
                    'public_electric' => $public_electric,
                    'costAll' => $costAll,
                    'type' => 0
                );
                array_push($arr, $hydropwerCost);
            }
    
            $hydropwerCost = $this->ApplicationForm_model->hydropwerCost($arr);
            $hydropwerCost = json_decode($hydropwerCost);
    
            //取费用明细
            $cost = $this->ApplicationForm_model->monthmandayDetail($beginDate, $endDate);
            $cost = json_decode($cost);
    
            if($hydropwerCost->res == '40000')
            {
                return json_encode(array('res' => '40000', 'msg' => '月底结算成功', 'data' => $cost->data));
            }
            else
            {
                return json_encode(array('res' => '40001', 'msg' => '月底计算失败', 'data' => ''));
            }
        }
    }


    /**
     * @description: 月底费用计算
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function mothEndCalc()
    {
        $date = $_POST['date']; //核算日期

        // $date = "2020-10-25";

        $result_json = $this->monthCalc($date);

        echo $result_json;

        return 0;
    }

    // 个人费用导出
    // public function excel_cost1()
    // {
    //     $date = "2020-10-25"; 
    //     $this->excel_cost($date);
    // }

    /**
     * @description:个人水电费用导出
     * @param {$date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function excel_cost($date = FALSE)
    { 

        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值
        $objActSheet->setCellValue('A1','日期');
        $objActSheet->setCellValue('B1','区域名称');
        $objActSheet->setCellValue('C1','宿舍楼名称');
        $objActSheet->setCellValue('D1','宿舍楼层号');
        $objActSheet->setCellValue('E1','宿舍房间号');
        $objActSheet->setCellValue('F1','工号');
        $objActSheet->setCellValue('G1','姓名');
        $objActSheet->setCellValue('H1','本月人天数');
        $objActSheet->setCellValue('I1','个人热水费');
        $objActSheet->setCellValue('J1','个人冷水费');
        $objActSheet->setCellValue('K1','个人电费');
        $objActSheet->setCellValue('L1','公共热水费');
        $objActSheet->setCellValue('M1','公共冷水费');
        $objActSheet->setCellValue('N1','公共电费');
        $objActSheet->setCellValue('O1','总费用');

        $num = 2;

        $data = $this->ApplicationForm_model->personCost($date, FALSE);
        $data = json_decode($data,true);

        for ($i=0; $i < count($data['msg']); $i++) 
        { 
            $objActSheet->setCellValueExplicit('A' . $num,$data['msg'][$i]['area_name'] , PHPExcel_Cell_DataType::TYPE_STRING);           //区域名称
            $objActSheet->setCellValueExplicit('B' . $num,$data['msg'][$i]['floor_name'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼名称
            $objActSheet->setCellValueExplicit('C' . $num,$data['msg'][$i]['cost_date'] , PHPExcel_Cell_DataType::TYPE_STRING);           //日期
            $objActSheet->setCellValueExplicit('D' . $num,$data['msg'][$i]['dorm_floor'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼层号
            $objActSheet->setCellValueExplicit('E' . $num,$data['msg'][$i]['room_number'] , PHPExcel_Cell_DataType::TYPE_STRING);         //宿舍房间号 
            $objActSheet->setCellValueExplicit('F' . $num,$data['msg'][$i]['jobNumber'], PHPExcel_Cell_DataType::TYPE_STRING);            //工号
            $objActSheet->setCellValueExplicit('G' . $num,$data['msg'][$i]['name'], PHPExcel_Cell_DataType::TYPE_STRING);                 //姓名
            $objActSheet->setCellValueExplicit('H' . $num,$data['msg'][$i]['month_dayall'] , PHPExcel_Cell_DataType::TYPE_STRING);        //本月人天数
            $objActSheet->setCellValueExplicit('I' . $num,$data['msg'][$i]['person_hotwater'], PHPExcel_Cell_DataType::TYPE_STRING);      //个人热水费
            $objActSheet->setCellValueExplicit('J' . $num,$data['msg'][$i]['person_coldwater'], PHPExcel_Cell_DataType::TYPE_STRING);     //个人冷水费
            $objActSheet->setCellValueExplicit('K' . $num,$data['msg'][$i]['person_electric'], PHPExcel_Cell_DataType::TYPE_STRING);      //个人电费
            $objActSheet->setCellValueExplicit('L' . $num,$data['msg'][$i]['public_hotwater'], PHPExcel_Cell_DataType::TYPE_STRING);      //公共热水费
            $objActSheet->setCellValueExplicit('M' . $num,$data['msg'][$i]['public_coldwater'], PHPExcel_Cell_DataType::TYPE_STRING);     //公共冷水费
            $objActSheet->setCellValueExplicit('N' . $num,$data['msg'][$i]['public_electric'], PHPExcel_Cell_DataType::TYPE_STRING);      //公共电费
            $objActSheet->setCellValueExplicit('O' . $num,$data['msg'][$i]['costAll'], PHPExcel_Cell_DataType::TYPE_STRING);              //总费用
            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
            $num = $num + 1;
        }

        //设置列的属性  
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("J")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("K")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("L")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("M")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("N")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("O")->setWidth(20); // 设置宽度 
        
        $outputFileName = iconv('UTF-8', 'gb2312', '水电结算信息.xlsx');  

        //文件直接输出到浏览器 
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');

        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objExcel = null;
    }

    /**
     * @description: 水电表房间数据导出
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function waterMeter_excel($date = FALSE)
    {
        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值
        $objActSheet->setCellValue('A1','日期');
        $objActSheet->setCellValue('B1','区域名称');
        $objActSheet->setCellValue('C1','宿舍楼名称');
        $objActSheet->setCellValue('D1','宿舍楼层号');
        $objActSheet->setCellValue('E1','宿舍房间号');
        $objActSheet->setCellValue('F1','房间热水表');
        $objActSheet->setCellValue('G1','房间冷水表');
        $objActSheet->setCellValue('H1','房间电表');
        $objActSheet->setCellValue('I1','房间类型');

        $num = 2;

        $data = $this->ApplicationForm_model->waterMeter($date, FALSE);
        $data = json_decode($data,true);

        for($j=0; $j < count($data['msg']); $j++)
        {
            if($data['msg'][$j]['type'] == 0)
            {
                $data['msg'][$j]['type'] = '公共房间';
            }
            else
            {
                $data['msg'][$j]['type'] = '非公共房间';
            }
        }

        for ($i=0; $i < count($data['msg']); $i++) 
        { 
            $objActSheet->setCellValueExplicit('A' . $num,$data['msg'][$i]['date'] , PHPExcel_Cell_DataType::TYPE_STRING);                //日期
            $objActSheet->setCellValueExplicit('B' . $num,$data['msg'][$i]['area_name'] , PHPExcel_Cell_DataType::TYPE_STRING);           //区域名称
            $objActSheet->setCellValueExplicit('C' . $num,$data['msg'][$i]['floor_name'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼名称
            $objActSheet->setCellValueExplicit('D' . $num,$data['msg'][$i]['dorm_floor'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼层号
            $objActSheet->setCellValueExplicit('E' . $num,$data['msg'][$i]['room_number'] , PHPExcel_Cell_DataType::TYPE_STRING);         //宿舍房间号 
            $objActSheet->setCellValueExplicit('F' . $num,$data['msg'][$i]['room_hotwater'], PHPExcel_Cell_DataType::TYPE_STRING);        //房间热水表
            $objActSheet->setCellValueExplicit('G' . $num,$data['msg'][$i]['room_coldwater'], PHPExcel_Cell_DataType::TYPE_STRING);       //房间冷水表
            $objActSheet->setCellValueExplicit('H' . $num,$data['msg'][$i]['room_electric'] , PHPExcel_Cell_DataType::TYPE_STRING);       //房间电表
            $objActSheet->setCellValueExplicit('I' . $num,$data['msg'][$i]['type'], PHPExcel_Cell_DataType::TYPE_STRING);                 //房间类型
            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
            $num = $num + 1;
        }

        //设置列的属性  
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        
        $outputFileName = iconv('UTF-8', 'gb2312', '房间水电表信息.xlsx');  

        //文件直接输出到浏览器 
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');

        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objExcel = null;

    }

    /**
     * @description: 公共水电表数据导出
     * @param {date}
     * @return json:array('res'=>'','msg'=>'','data'=>'')
     */    
    public function publicCost_excel($date = FALSE)
    {
        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值
        $objActSheet->setCellValue('A1','日期');
        $objActSheet->setCellValue('B1','区域名称');
        $objActSheet->setCellValue('C1','宿舍楼名称');
        $objActSheet->setCellValue('D1','宿舍楼层号');
        $objActSheet->setCellValue('E1','空气能用电');
        $objActSheet->setCellValue('F1','空气能用水');
        $objActSheet->setCellValue('G1','当前层总水表度数');
        $objActSheet->setCellValue('H1','当前层总电表度数');
        $objActSheet->setCellValue('I1','洗衣机费用');
        $objActSheet->setCellValue('J1','当前层总人天');
        $objActSheet->setCellValue('K1','宿舍总电表');
        $objActSheet->setCellValue('L1','宿舍总水表');

        $num = 2;

        $data = $this->ApplicationForm_model->publicCost($date, FALSE);
        $data = json_decode($data,true);


        for ($i=0; $i < count($data['msg']); $i++) 
        { 
            $objActSheet->setCellValueExplicit('A' . $num,$data['msg'][$i]['public_date'] , PHPExcel_Cell_DataType::TYPE_STRING);         //日期
            $objActSheet->setCellValueExplicit('B' . $num,$data['msg'][$i]['area_name'] , PHPExcel_Cell_DataType::TYPE_STRING);           //区域名称
            $objActSheet->setCellValueExplicit('C' . $num,$data['msg'][$i]['floor_name'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼名称
            $objActSheet->setCellValueExplicit('D' . $num,$data['msg'][$i]['dorm_floor'] , PHPExcel_Cell_DataType::TYPE_STRING);          //宿舍楼层号
            $objActSheet->setCellValueExplicit('E' . $num,$data['msg'][$i]['airElectricity'] , PHPExcel_Cell_DataType::TYPE_STRING);      //宿舍房间号 
            $objActSheet->setCellValueExplicit('F' . $num,$data['msg'][$i]['airWater'], PHPExcel_Cell_DataType::TYPE_STRING);             //房间热水表
            $objActSheet->setCellValueExplicit('G' . $num,$data['msg'][$i]['floor_coldwater'], PHPExcel_Cell_DataType::TYPE_STRING);      //房间冷水表
            $objActSheet->setCellValueExplicit('H' . $num,$data['msg'][$i]['floor_electricty'] , PHPExcel_Cell_DataType::TYPE_STRING);    //房间电表
            $objActSheet->setCellValueExplicit('I' . $num,$data['msg'][$i]['washingMachine'], PHPExcel_Cell_DataType::TYPE_STRING);       //房间类型
            $objActSheet->setCellValueExplicit('J' . $num,$data['msg'][$i]['floor_dayall'], PHPExcel_Cell_DataType::TYPE_STRING);         //房间类型
            $objActSheet->setCellValueExplicit('K' . $num,$data['msg'][$i]['dorm_totalElectricty'], PHPExcel_Cell_DataType::TYPE_STRING); //房间类型
            $objActSheet->setCellValueExplicit('L' . $num,$data['msg'][$i]['dorm_totalWater'], PHPExcel_Cell_DataType::TYPE_STRING);      //房间类型
            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
            $num = $num + 1;
        }

        //设置列的属性  
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("J")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("K")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("L")->setWidth(20); // 设置宽度 
        
        $outputFileName = iconv('UTF-8', 'gb2312', '公共水电表信息.xlsx');  

        //文件直接输出到浏览器 
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');

        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objExcel = null;

    }
}



?>